Night Owl 6/Night Owl's Shareware - PDSI-006 - Night Owl Corp (1990).iso/025a/crdepp.zip/CRDE.DOC

Text File | 1991-01-05 | 305KB | 10,880 lines

    The

    C

    Relational

    Database

    Engine









    Copyright 1990, Mark Lang.















Table of Contents



Chapter 1, Introduction    ...............................................    1

Chapter 2, Getting Started ............................................    3
       System Requirements ............................................    3
       Program capacities .............................................    3
       Installing CRDE ................................................    3
       What comes with CRDE ...........................................    4
       Using CRDE with Turbo C ........................................    5
         Linking  CRDE ............................................    5
         Reserved function calls ..................................    5
         Using CRDE in your program    ...............................    5
         Using disk caches with CRDE ..............................    6
       CRDE tables ....................................................    6
       Basic terminology ..............................................    7

Chapter 3, Creating Tables ............................................    8
       The TABLE type .................................................    8
       Table descriptors ..............................................    9
       Defining a primary key .........................................    10
       Creating a database ............................................    12
          The kitchen sink approach    ...............................    12
          The relational approach .................................    13

Chapter 4, Filling Tables with Information ............................    14
       Loading  rows with tload    .......................................    14
       Error -41 ......................................................    15
       Inserting rows .................................................    16
       Replacing rows .................................................    17
       Write modes ....................................................    17

Chapter 5, Basic Table I/O ............................................    19
       Opening tables .................................................    19
       Integrity checking .............................................    19
       Closing  a table    ...............................................    19
       Checking table accessability ...................................    20
       Renaming tables ................................................    20
       Erasing tables .................................................    20
       Secondarr indexes ..............................................    20
          Creating indexes ........................................    20
          Index descriptors    .......................................    21
          Primary index vs Secondary indexes ......................    22
          Rebuilding indexes ......................................    23
          Dropping indexes ........................................    23
          Redundant indexes    .......................................    23

Chapter 6, Viewing a Table ............................................    24





Chapter 7, The Search-Related Functions    ...............................    26
       The seven search related functions .............................    26
       The search expression ..........................................    26
       The search engine ..............................................    27
       Search related operations ......................................    27
          Selecting rows ..........................................    27
          Projecting rows .........................................    28
          Deleting rows ........................................... 29
          Changing rows ........................................... 30
          Scanning rows ...........................................    30
          Looking up a row ........................................    31
          Getting rows into C variables ...........................    31
       "..if" functions    ...............................................    32

Chapter 8, Miscelleneous operations ...................................    36
       Restructuring tables ...........................................    36
       Renaming columns    ............................................... 37
       Getting info from your tables ..................................    38
       Importing and Exporting data ................................... 39
          Ascii files .............................................    39
          dBASE files .............................................    41

Chapter 9, Working with Dates ......................................... 43
       The date_t type and date math ..................................    43
       Converting calender dates ......................................    43
       Miscelleneous date functions ...................................    44
       Month and year math ............................................    45
       Day of week functions ..........................................    45

Chapter 10,  Temporary Tables .........................................    47
       STATIC tables ..................................................    47
       Virtual tables .................................................    47
       TEMP tables and auto-cleanup ...................................    48
       Rules for using TEMP tables ....................................    49

Chapter 11, Performing Queries ........................................    50
       What is a query?    ...............................................    50
       Simple Queries    ...............................................    52
          Simple select queries ................................... 52
          ORing queries ........................................... 53
          Projecting columns ......................................    54
          Selecting distinct values    ............................... 55
          Joining tables ..........................................    55
          Order By queries ........................................ 57
          Statistical queries, making calculations ................    58
          Set-related queries .....................................    59
       Complex Queries ................................................ 60
          Subqueries ..............................................    60
          Correlated subqueries ...................................    60
          Multi-table joins    ....................................... 61
          "In" subqueries ......................................... 62
          Multiple subqueries ..................................... 62
          Group By queries ........................................ 63
             Writing grouping functions    .......................    64
             Group Having queries ............................. 68
          Set Group queries    ....................................... 69
       Optimizing Queries ............................................. 73



Chapter 12, Creating Reports .......................................... 74
       Single table reports ........................................... 74
       Multi-table reports ............................................    75

Chapter 13, Maintaining Database Integrity ............................    79
       Entity integrity    ...............................................    79
       Domain integrity    ...............................................    79
       Referential integrity .......................................... 80
          Foreign an primary keys ................................. 80
          and inserting rows ...................................... 80
          and deleting rows    ....................................... 81
          and changing rows    ....................................... 82

Chapter 14, Performing Tansactions ....................................    84
       CRDE's transaction tracking features ...........................    84
       How does transaction tracking work? ............................    85

Chapter 15, Reparing Tables ...........................................    86
       Corrupted indexes ..............................................    86
       Testing your indexes ...........................................    86
       Repairing a table with trepair .................................    86
       Limitations of trepair .........................................    87

Reference Guide    .......................................................    88
       Standard Conventions ...........................................    88
          Return values ...........................................    88
          Functions which return tables ...........................    89
          Parameters in CRDE functions ............................ 90
          Auto-cleanup of TEMP tables .............................    90
       Function library    ...............................................    92

Global Variables ......................................................    170

Appendix A, Error Codes    ...............................................    173










Chapter 1

Introduction



The C Relational Database Engine (called CRDE hereafter) is a library
of over 90 functions which support the creation, maintenance, and
querying of single-user databases.  CRDE is not another B-tree library.
It goes far beyond the capabilities of most data management libraries.
Complex operations such as sorting, restructuring, and joining tables
are single line function calls in CRDE.  In terms of ease of use,
relational querying power, and sheer speed, CRDE is on par with
professional relational database managent systems.

Below are listed just some of the features of CRDE:

    CRDE uses a sophisticated file and memory manager to
    manage  tables and indexes. The manager intelligently
    coordinates all  your resources for maximum performance. It
    also features support  for transaction tracking, without a
    network.

    CRDE tables each have their own data dictionary which
    contains  information about the columns and indexes for a
    table. Among other things, CRDE uses  this information to
    maintain and use indexes transparently.

    CRDE can match the relational querying power of high level
    query  languages such as SQL, often in as little as one or two
    function calls.  However CRDE is not an SQL engine and
    does not require a  preprocessor.

    CRDE requires no special initialization calls or setup, and is
    not  a TSR.  Simply link the library and use the functions.

    CRDE uses C data types and stores data as C structures,
    making  sharing data between C and CRDE tables very easy.

    CRDE has a complete date library for managing dates and
    performing date math.

    CRDE introduces the TABLE datatype which is the heart of
    CRDE.  A TABLE is very similar to a FILE in terms of use
    and design,  although much more powerful. Functions can
    accept tables as  parameters and return tables as results.

    CRDE comes with a view facility which you can integrate
    directly into  your programs. With it you can view any table
    in any sorted order,  selecting only those columns you wish to
    display, in any window you want to view the table in, with a
    single function call.


                     - 1 -


This manual assumes that you are a fairly experienced C programmer with
an understanding of the relational database model. Familiarity with a
high level relational database language and/or SQL is also recommended.
You should also be familiar with Turbo C 2.0 and how to link and use
library modules with your own programs.




















































                     - 2 -





Chapter 2

Getting Started



System Requirements

To use CRDE you must have all of the following:

 - An IBM PC, XT, AT, PS/2 or 100% compatible computer.

 - At least 512k of internal memory.

 - A hard drive.

 - DOS 2.0 or greater.

 - Turbo C 2.0.

You should also make sure that your CONFIG.SYS file contains the statement

    FILES = 20

in it.  Although this setting is not mandetory, it is the recommended
setting for using CRDE for optimal performance.


Program Capacities

In CRDE, a single table may have up to

 - 2 billion rows with up to 4000 bytes each.

 - 256 columns with up to 256 characters each.

 - 1 primary index composed of up to 8 columns.

 - 8 secondary indexes composed of up to 8 columns each.


Installing CRDE

Before you can use CRDE it must be installed on your computer. CRDE is
not copy-protected, so installation is a fairly straight-forward.
Simply copy the contents of the CRDE diskette into your working
directory.  For example, if your working directory is C:\TURBOC, place
the CRDE diskette into drive A: and type

    C:>copy A:*.* C:\TURBOC




                     - 3 -

to copy CRDE into your hard drive.  Specifically, Turbo C must be able to
access 3 files on your CRDE diskette, crde.lib, crde.h, and date.h.


What comes with CRDE

There are 18 files on the CRDE diskette.  Only the first three are
required to create and compile applications with CRDE. The rest are
sample programs which you may run and examine at your convenience.

crde.lib    This is the CRDE library. You must link this to any
        application which uses CRDE.

crde.h       This header file contains prototypes of the bulk of CRDE
        functions plus several pre-defined macros used by CRDE.
        Include this in any source file which uses CRDE functions.

date.h      This header file contains prototypes for all the date
        functions in the CRDE library. It also defines the
        date_t type used by CRDE. Include this in any source
        file which uses any of CRDE's date functions or the
        date_t datatype.

read.me            Ascii file containing last minute information about
        bug-fixes or updates not found in the CRDE manual which
        can be loaded into virtually any editor for inspection.
        Your CRDE diskette may or may not contain this file.

sample.exe     The sample application provided with CRDE which
        illustrates some of CRDE's features.  sample.exe
        maintains a small, fully-functional, library database.

sample.c    Source code to the sample application.

sample.prj       Project file for creating the sample application.

tview.c        Program written using CRDE functions to view any CRDE
        table from the command line.

tstruct.c     Program written using CRDE functions to view any CRDE
        table's structure from the command line.

texport.c     Program written using CRDE functions to export any CRDE
        table to ascii or dBASE format.














                     - 4 -

Using CRDE with Turbo C

Linking  CRDE

To use CRDE include the crde.h header file in any source file containing
CRDE function calls. You may also need to include the date.h file if
you use any of CRDE's date functions. Programs written using CRDE
functions must be compiled in the LARGE memory model and linked to the
CRDE library crde.lib.  To do this from the integrated environment you
must create a project file to let Turbo C know that you want it to link
the CRDE library with your program.  An example project file to create
an executable file for a program "noname.c" might look like the following:

    noname
    crde.lib

See your Turbo  C user's manual for more information on creating project
files.  You can also link you program from the command line using TLINK.
To create an executable file using TLINK for the same program "noname.c"
would look something like this:

  tlink  noname.obj c0l.obj,,, emu.lib mathl.lib cl.lib crde.lib

It is also a good idea when compiling programs using CRDE to set the
"Options\Linker\Warn duplicate symbols" On. This will keep you from
accidentally duplicating a CRDE function within your own program and
causing a program crash.


Reserved function calls

The CRDE library is made up of a central engine TENGINE, plus object
code for each CRDE function.  TENGINE consists of 100 "engine calls"
which are used reserved specifically for use by CRDE functions.  The
CRDE engine calls have the format _CRDE_XX where XX is a number from
00 to 99.  Calling any of these routines directly will most likely
cause your program to hang.


Using CRDE in your program

CRDE requires no special initialization or setup.  Simply link the
library and use the functions.  However, every program which uses CRDE
must contain and assign the global variable tbuffers.   This variable
tells CRDE how much memory to set aside for its exclusive use.  CRDE
allocates memory in 512 byte blocks or buffers.  For example, the
following would set aside 64k of memory for use by CRDE's functions:

    int tbuffers = 128;

    int main()
    {
        /* program here */
    }




                     - 5 -

tbuffers must be declared globally.  CRDE requires the tbuffers
variable to run.  Fortunately, the linker will tell you if you forget
to declare tbuffers.

Assigning a good value to tbuffers depends upon many factors:  how much
memory is avaiable, the number and sizes of your tables, how much memory
your program needs, if you do large transactions on tables, etc.  A
good minimal value for most programs is 128 buffers.  This value
provides solid performance for most operations and only uses 64k of
memory.   For optimal performance in programs which are database
intensive or make use of CRDE's transaction tracking facilities should
consider a setting of 256 or 512 buffers or more if memory allows.
CRDE requires a minium of at least 64 buffers (32k) to run efectively.

Note:  CRDE allocates its buffers according to tbuffers automatically
after the first table is opened or created.  Changing the value of
tbuffers after that will not effect the amount of buffers used by CRDE.


Using disk caches with CRDE

Do not use disk caches, heap expanders, or similar types of programs in
conjunction with CRDE.  CRDE has a sophisticated built-in file and memory
manager which has all the capabilities of the above programs and more.
CRDE knows about which tables and indexes will involved in an operation
while these other program do not.  Using them will interfere with CRDE
and will probably impede performance rather than improve it.


CRDE tables

A CRDE table consists of one or more files.  Specifically, a table can
consist of the table itself, a primary index, and up to 8 secondary
indexes.  Although each of these objects consists of a separate file on
disk, CRDE considers them to be a unit.

On disk, all the objects related to a single table have the same base
name as the table and are distinguished from each other by their
filename extension.

    Extension     Type of Object

    .t        table
    .p         primary index
     .sX        secondary index

The X in a secondary indexes extension stands for a digit from 0 to 7.
A table's base name may be any legal DOS filename, but may not include
wildcards or an extension.









                     - 6 -

Basic Terminology

The following is a set of basic terms you should be familiar with before
using CRDE.

database    A database is a collection of information or data
        pertaining to a specific project.  For instance, a
        library database would contain all the information
        about books, members, loans, etc. in the library.  A
        relational database consists of one or more tables.

table        A table is a discrete set of information within a
        database.  A table can be visualized as a two
        dimensional chart with rows and columns.  In  C, a
        table might be more analogous to an array of
        structures.  CRDE also considers any indexes created
        on the table to be part of the table.

row        A row is a horizontal slice of a table. In C, a row is
        similar to an item in an array.  The terms row and
        record are interchangeable in CRDE.

column         A column is a vertical slice of a table.  In C, a
        column is similar to a single field in a structure.
        The terms column and field are interchangeable in CRDE.

index        An index is a special file organized for quick access
        to information in a table.  An index on a table is
        similar to an index in a book.  CRDE can use an index
        to lookup information very quickly.

key        A key describes the columns which make up an index.  In
        CRDE, up to 8 columns can be part of a key.

























                     - 7 -






Chapter 3

Creating Tables



The TABLE type

CRDE introduces a new data type, TABLE, which is much akin to the FILE
type defined in the C standard library, although much more powerful,
as you shall see.  Tables can be assigned to variables, passed as
parameters, and returned by functions.  This object-oriented approach
to data management, including the casual way in which you can create
and manipulate tables, is at the heart of CRDE's power.  You can
declare variable as TABLE * just as you would a FILE *. For example,
to declare variable t as a TABLE would look like the following:


    TABLE *t;

Note:  you cannot declare a variable to be of type TABLE.  It must be
a TABLE *.  Doing so will cause a compilation error.

You can create a new table with the tcreat function.  The following
program creates a simple table called "sups" containing 4 columns: s#,
sname, city, status, and assigns it to t.

    #include <stdio.h>
    #include "crde.h"

    int main()
    {
      TABLE *t;

      t = tcreat("customer", "s# i, sname c26, city c26, status i");
      tclose(t);
    }

tcreat is declared in crde.h as:

    TABLE *tcreat(char *name, char *td);

The first parameter name is the name of the table.  name must be a legal
DOS pathname <= 64 characters. It may not contain wildcards or an
extension.








                     - 8 -

Table descriptors

The second parameter td is known as the table descriptor.  The table
descriptor defines the columns in the table, their names, types and
sizes.  A table descriptor should be an asciiz string of the format:

    "colname1 coltype1 colname2 coltype2 colname3 coltype3 ..."

Column names can consist of any characters except whitespace.  White-
space characters are all characters classified by the standard library
function isspace() plus the comma ','.  Thus column names can contain
letters, numbers, and other characters like '#' and '-'.  However, you
should avoid using the '*' and '!' characters in column names since
they have special meaning in CRDE.  A table may contain up to 256
columns.

A column name should describe the contents of the column.  A table may
not contain two columns with the same name.  Column names are case-
sensitive however, so "Name", "NAME", and "name" are all considered
different.  Every column name in a table descriptor must be followed
by a datatype which defines the type of the column. Column names and
types must be individual tokens, i.e. separated by whitespace.

CRDE supports 6 datatypes:  c, i, l, d, f, and $.  Each type directly
relates to a native C datatype.

c       A character string between 1 and 256 characters. The c datatype
    must be immediately followed by a number between 1 and 256.
    This is the maximum length of the string. The c type is
    equivalent to the C type char[].  Thus the type c26 is equivalent
    to char[26], and so on.

i       An integer value corresponding to the C type int.  In Turbo C,
    an int has a range from -32767 to 32767.

l       A long integer value corresponding to the C type long int.  In
    Turbo C, a long int has a range from -2147483647 to 2147483547.

d       A date.  The d type corresponds to the date_t type defined in
    date.h.  The Turbo C version of date_t is equivalent to type
    long int.  CRDE supplies several functions for creating and
    manipulating dates.  A date type can hold any date between
    1/1/1 and 12/31/32767.

f       A floating point value.  The f type directly corresponds to the
    C type double.  In Turbo C, a double has a range of 1.7E-308 to
    1.7E+308.


$       A dollar value. The $ type corresponds to the dollar_t type
    defined in crde.h, which in the Turbo C version is type double.







                     - 9 -

Since all CRDE datatypes directly correspond to C datatypes, it makes
sense that a row in CRDE then, would correspond to a C structure.  This
is an important feature of CRDE, making transferring data between CRDE
tables and C variables relatively easy.  A row in the sups table would
correspond to the C declaration

    struct {
        int s;
        char sname[26];
        char city[26];
        int status;
    }

This becomes important when you want to transfer data between C
variables and CRDE tables.  Note: since C variable names cannot contain
characters like '#', they were omitted from the declaration. In fact,
none of the column names have to appear in the structure, only their
types and sizes are important.


Defining a primary key

CRDE supports the relational idea of defining a primary key on a table.
The primary key is made of one or more column(s) of the table.  A table
which has a primary key is generally referred to as being a keyed table.
Similarly, a table without a primary key is referred as being non-keyed.
The key values of a row in the table consist of all the values in every
column in the row which make up the primary key.  By definition, every
row in a keyed table can be uniquely identified by its key value(s).
Consequently, no two rows in a table can have the same key value(s).

A primary key prevents you from adding rows to a table which have the
same key values.  In CRDE, up to 8 columns may be part of the primary
key. CRDE prevents two or more rows in a table from having identical
values in each of the assigned columns.  CRDE also supports non-keyed
tables (tables which do not have a primary key).  It is good relational
practice, however, to define a primary key on a table.  Doing so will
help you to normalize your tables and have a minimum amount of redundant
information, and can also improve search times.  Tables are usually
linked to one another by all or part of their primary keys.

You can define a primary key in a table descriptor by prepending any
columns you want as part of the key with an asterisk '*'.  The '*' must
be directly attached to the column's name, and not be separated by
whitespace.  Columns which are part of the primary key can be in any
order, and do not have to be consecutive.  However, the order can have
other side effects. In general, you should place all key columns at the
beginning of a table, with the most significant column first, the next
most significant column, second, etc.  In the sups table above, the "s#"
column is the key field of the table. It uniquely identifies a row, thus
every row should have a unique value for "s#".  To make "s#" the primary
key of the table you would define the table as

    t = tcreat("sups", "*s# i, sname c26, city c26, status i");

This would prevent you from entering to suppliers who have the same "s#".


                     - 10 -

As previously stated, a primary key can be made of up to 8 columns.
This orders table contains a supplier # "s#", a part# "p#" and a
quantity "qty".  The primary key is on both "s#" and "p#".

    t = tcreat("orders", "*s# i, *p# i, qty i");

This table would allow you to have orders with the same supplier # or
part # but not both.

Defining a primary key in CRDE has another advantage as well.  To
enforce the primary key, CRDE creates an index, called the primary
index, on the table.  Whenever you insert a row into a table, CRDE
uses this index to make sure that the primary key will not be
violated. However, CRDE can also use this index to help speed up
searches and other relational operations.  Since the bulk of relational
operations will involve the primary key of a table, this will
significantly improve the performance of many operations.  You never
have to remind CRDE to update the primary index. CRDE updates it
automatically whenever you insert, change, or delete rows in a table,
using any CRDE function, so it is always kept up to date.

When designing a table, your first thought should be of what will be
the table's primary key.  The key should be a value which would not be
duplicated under normal circumstances, such as an id# or some other
unique identification value.  A poor choice for a primary key, usually
made by beginners, is a name or some other large character field.
But who is to say that two suppliers might not have the same name?
Furthermore, you might accidently misspell the name, or use the wrong
case, leave trailing blanks on the end of it, making it difficult to
correctly identify duplicate keys.

The best choices for key columns are columns which have the types i,
l, and d.  Short c columns can also make good keys if used correctly
(for example, an id which contains letters in it, such as a serial#).
c columns longer 15 or 20 characters are probably poor choices for keys,
although CRDE support keys up to 256 characters in length.  Not all
columns in the primary key do not have to be of the same type either.
You may frequently have primary keys which are made of i, l, d, and c
columns in combination.

f and $ columns are the worst choices for primary keys, although CRDE
does support them as such. This is because of the nature of floating
point values on computers. A computer can only measure a floating point
value to a certain number of significant digits, whereas it can
calculate i, l, c, and d columns exactly. Thus, the results of floating
point expressions are not always accurate.  Rather they are
approximations of what the result should be.  Consider the problem of
a simple floating point division:

    double f = 1.0 / 3.0;

The answer to this problem is f = 0.333333333 etc...  But since the
computer can only store 19-20 digits, it must truncate the value.  The
result is not really 1.0 / 3.0, but an approximation.  Two approximations
of the same value might not always be identical.  The bottom line is
that you should avoid using f or $ columns in primary keys if at all
possible.

                     - 11 -

Creating a database

Creating a good relational database can be challenging, especially if
you are not familiar with the relational model.  Beginners will often
try to lump all data together into a single table rather than distribute
data among several tables and relate them together.  This can lead to
databases which are very redundant and inflexable.


The kitchen sink approach

Suppose you wanted to maintain a list of orders made for parts from
various suppliers.  Your first draft of the database looks like this

    orders = tcreat("orders", "sname c26, city c26, status i,"
      "pname c26, color c11, qty i");

Each order contains the supplier's name, city, and  status, the part's
name and color, and the quantity ordered.  This seems harmless enough.
Now lets make some orders:

sname        city        status     pname        color    qty
---------------    ---------------    -------    ---------------    -------    -------
Smith, John    Boston        5    bolt        red    100
Smith, John    Boston        5    screw        red    200
Smith, John    Boston        5    screw        blue    300
Smith, John    Boston        5    nut        gray    400
Smith, John    Boston        5    bolt        gray    500
Smith, John    Boston        5    nail        red    600
Jones, Ben    New York    10    bolt        red    100
Jones, Ben    New York    10    screw        red    100
Jones, Ben    New York    10    nail        red    100
Barnes, Frank    Boston        15    bolt        red    200
Barnes, Frank    Boston        15    screw        red    50
Landon, Mark    Auburn        20    screw        blue    100
Landon, Mark    Auburn        20    bolt        gray    500


As you add more and more information to the table, the problems with
this arrangement become apparent.  There is a large amount of redundant
information in the table.   The same names and cities appear several
times in the table.  Every time an order is entered, redundant
information is added over and over again.

This type of organization is also very inflexible.  For example, if
John Smith moved to Chicago, you would have to go through and change
every row which contained John Smith.  Suppose another supplier named
John Smith lived in Boston.  Although unlikely, it is not impossible.
There would be no way to differentiate between the two.









                     - 12 -

The relational approach

The relational solution to this problem is to divide the one large
table into smaller tables by organizing data into groups:  suppliers,
parts, and orders.  Suppliers and parts are given special id #'s so
that they may be easily identified.  The orders table references the
supplier and part tables though its "s#" and "p#" columns.

    sups = tcreat("sups", "*s# i, sname c26, city c26, status i");
    parts = tcreat("parts", "*p# i, pname c16, color c11");
    orders = tcreat("orders", "*s# i, *p# i, qty i");

sups

    s#     sname        city        status
    -------    --------------- ---------------    -------
    1    Smith, John     Boston        5
    2    Jones, Ben      New York    10
    3    Barnes, Frank    Boston        15
    4    Landon, Mark    Auburn        20
    5    Andrews, Jim    New York    5


parts

    p#    pname        color
    -------    ---------------    -----------
    1    bolt        red
    2    screw        red
    3    screw        blue
    4    nut        gray
    5    bolt        gray
    6    nail        red

orders

    s#    p#    qty
    -------    -------    -------
    1    1    100
    1    2    200
    1    3    300
    1    4    400
    1    5    500
    1    6    600
    2    1    100
    2    2    100
    2    6    100
    3    1    200
    3    2    50
    4    3    100
    4    5    500


Breaking up your tables in this manner is called normalization.  A table
is said to be normalized if it contains a minimum amount of redundant
information.


                     - 13 -






Chapter 4

Filling Tables with
Information



Loading tables with tload

Now that we have a sample database set up, we need to fill it with
information.  CRDE provides 3 functions for inputing data into tables:
tload, tinsert, and treplace.  The simplest of the three is tload.
tload is declared in crde.h as

    int tload(TABLE *t, ...);

tload expects a variable series of arguments, one for every column in t.
Together the arguments form a row.  tload then inserts the row into the
table.  For example, to load a supplier s# = 1, sname = "Smith, John",
city = "Boston", status = 5, into the sups table would look like

    tload(sups, 1, "Smith, John", "Boston", 5);

The table sups now contains one row.  Data must be entered for every
column in the exact same order that they appear in the table's table
descriptor, and you may not skip column(s).  The argument for each
column must be the exact type for that column (c columns expect a
char[], or char *, as an argument).  If an argument is not of the
correct type, the data you are loading may not be what you expect.

This problem can commonly occur in l columns.  For example, suppose
the sups table were defined as

    sups = tcreat("sups", "*s# l, sname c26, city c26, status i");

the above tload statement would be invalid.  Why?  because the first
argument is an int value whereas the "s#" column is of type l or long
int.  By default, C converts constant 1 into an  int.  To load 1 as the
suppler number you would have to explicitly cast it as type long by
using a typecast (long int) or the L suffix.  A similar problem would
occur is "s#" were of type f  (which in this case it  shouldn't be
since it is part of the primary key).  A way around having to use a
specific typecast with f columns is to let the compiler know that you
are entering a floating-point value by writing the number as 1.0.
C interprets floating point constants to be of type double by default.
In a similar vein, you should be sure to make sure that the constant
value is not out of range.  For example, loading 1000000 into an int
column (in Turbo C anyway).  tload does handle the problem of char *





                     - 14 -

arguments which are too long however. If the argument for a c column
to too large to fit in the column, tload will automatically truncate
it to fit into the column's specified length.  tload does no type
checking, nor does it verify that the correct number of arguments have
been entered.  Entering the wrong number of arguments or the wrong type
for an argument will leave part or all of the columns in the row
undefined.

You can load any datatype into tables, even date types.  For example,

    emps = tcreat("emps", "e# i, ename c26, hired d, salary f");
    tload(emps, 100, "Barnes, Don", mkdate(4, 2, 1978), 35000.0);
    tload(emps, 101, "Carnegie, Jamie", mkdate(5, 7, 1988), 25000.0);
    tload(emps, 102, "Donald, Markus", mkdate(11, 13, 1985), 42500.0);

mkdate is declared in date.h.  It receives three parameters, month,
day, and year, and returns the date_t equivalent.  Also note how the
salaries and appended with a .0 to mark them as double values.  You
can also use variables in tload.  Using variables can be safer than
constants because C converts a constant to the correct type when it
assigns a variable.

The following function demonstrates this point by loading a supplier
into the sups table based upon the parameters received.  Any constants
passed to LoadSup are automatically converted to the correct type by C
for you.  Using this method also eliminates any problems with entering
the correct number of arguments.  C (new-style anyway) will prevent you
from entering the wrong number and/or types into the row by issuing a
compilation error.

    TABLE *sups;

    int LoadSup(long s, char *sname, char *city, int status)
    {
        return tload(sups, s, sname, city, status);
    }

    sups = tcreat("sups", "*s# l, sname c26, city c26, status i");
    LoadSup(1, "Smith, John", "Boston", 5);

tload returns 0 if no errors occurred.


Error -41

The most common error which can occur while loading or inserting rows
is error -41.  Error -41 occurs whenever you try to insert a row into
a table that already contains the key found in the row you are trying
to insert.  For example

    i = tload(sups, 1, "Smith, John", "Boston", 5);
    i = tload(sups, 1, "Hurt, Frank", "Denver", 10);






                     - 15 -

The first tload statement will return 0 meaning that the row was
successfully loaded (unless a supplier with an "s#" 1 already exists).
The second tload statement however will return -41, meaning that a row
with the same key (1) already exists and that the row was not loaded.
Unlike other error codes, -41 is not really considered to be a error
but rather an indicator that the primary key would be violated by
inserting the row.  -41 is also the only error code which will not
cause a function to abort.  Normally when an error occurs, a CRDE
function will immediately halt, do any cleaning up necessary and return.
Not so with error code -41.  This is why sometimes a function will
return successfully, but also set terrno to -41.  All that means is
that during the course of operation, the function tried to insert one
or more row(s) into a table which already contained a row(s) with the
same key.


Inserting rows

CRDE provides another function for inserting values into a table
called tinsert.  tinsert is declared in crde.h as

    long tinsert(TABLE *t, void *rows, long n);

tinsert expects rows to point to a buffer containing the row you want
to insert.  The buffer should be a structure defined exactly like the
table's table descriptor.  For example, to insert John Smith into the
sups table with tinsert would look like

    typedef struct {
        int no;
        char sname[26];
        char city[26];
        int status;
    } sup;

    sup s;

    /* fill structure with values */
    s.no = 1;
    strcpy(s.name, "Smith, John");
    strcpy(s.city, "Boston");
    s.status = 5;

    /* insert structure into table */
    tinsert(sups, &s, 1);

tinsert returns the number of rows inserted, in this case 1. You can
insert entire arrays into tables using tinsert.  rows should point to
the first element in the array.  n is the number of elements to insert.
To insert an array of 10 suppliers into the sups table would look like

    sup s[10];

    /* fill s with info */
    tinsert(sups, &s, 10);



                     - 16 -

Any rows in the array for which a key already exists in the table are
not inserted.  tinsert will return the number of rows that were
actually inserted into the table.



Replacing rows

Normally when you try to insert a row into a table where a duplicate
key already exists, CRDE will not let you insert the row.  However,
sometimes you may want to throw out the existing row and replace it
with the new  row.  CRDE gives you this option to override this by
using the treplace function.  treplace is declared in crde.h as

    long treplace(TABLE *t, void *rows, long n);

treplace is identical to tinsert.  The only difference being that
tinsert inserts rows and treplace replaces them, which means if a row
with the same key as the row you are trying to insert already exists,
the old one is thrown out and the new one inserted.  If a duplicate
key does not exist, then treplace simply inserts the row into the table.

Note:  By default, tload always inserts rows. There is no complementary
function by which you can load values which replace rows.  If you want
to replace a row you must use treplace.


Write modes

Normally when you make call a CRDE function which alters a table, such
as tload, tinsert, or treplace, CRDE automatically writes those changes
to disk after every function call.  This is to protect data integrity.
If something were to go wrong (such as a power-outage) all your
information would be safely on disk.  In CRDE this is called normal
mode.  By default a table is in normal mode when it is opened or created.

However, there are some times when you may want to prevent CRDE from
instantly writing your changes to disk after every function call.  The
primary reason is speed.  Writing every change to disk as soon as it
occurs can take an enormous amount of time, especially if you are
making a lot of relatively small changes in succession.  That's why
CRDE has another mode, called write-cache, or writec,  mode.  If a
table is in this mode,  CRDE trys to keep as many changes buffered in
memory as possible, instead of writing them immediately to disk.

CRDE provides two functions for setting a table's mode to either normal
or write-cache. These are tnormal and twritec respectively.  twritec
puts a table in write-cache mode.  tnormal returns a table to normal
mode.  You usually use twritec to place a table in write-cache mode
when you are making "batch" changes, or several changes in succession.
For example, suppose you wanted to load the sups table with a bunch
of new suppliers,






                     - 17 -

    twritec(sups);
    tload(sups, 1, "Smith, John", "Boston", 5);
    tload(sups, 2, "Jones, Ben", "New York", 10);
    tload(sups, 3, "Barnes, Frank", "Boston", 15);
    tload(sups, 4, "Landon, Mark", "Auburn", 5);
    tload(sups, 5, "Andrews, Jim", "Rochester", 5);
    /* load rest of suppliers here */
    tnormal(sups);

tnormal returns the table to normal mode.  It also writes all changes
made while the table was in writec mode to disk.  You can explicitly
flush a table's unwritten buffers to disk at any without leaving writec
mode by using the tflush function.  Also, CRDE can flush a table's
buffers automatically if it needs more memory or if the buffers
become full.  You can use the tmode function to determine what mode a
table is currently in.  tmode will return one of four constants defined
in crde.h depending upon what mode the table is currently in.

    T_NORMAL        table is in normal mode.
    T_WRITEC        table is in write-cache mode.
    T_TEMP            table is a TEMP table.
    T_STATIC         table is a STATIC table.

T_TEMP and T_STATIC are only returned by specially designated temporary
tables which operate in their own special mode which is similar to
writec and cannot be changed.  Temporary tables are explained in detail
in Chapter 11.































                     - 18 -






Chapter 5

Basic Table I/O



Opening tables

This chapter describes some basic table operations such as opening
and closing tables, renaming tables, erasing tables, and indexing tables.
Once a table has been defined and exists on disk, you only need to open
it to use it again.  You open a table with the topen function.  For
example:

    TABLE *t;

    t = topen("sups");

topen is declared in crde.h as

    TABLE *topen(char *name);

topen expects the table's name as a parameter, which must be a legal DOS
pathname less than 64 characters (no wildcards or extension).  Once the
table is open, you can perform any operations on the table you wish.


Integrity checking

topen does a number of integrity checks to validate that the table is
not corrupt.  topen can detect almost any type of corruption in the
table, and will attempt to fix the problem if necessary.  The most
common type of corruption is an index which has become out of sync
with the table.  If topen detects this, it will automatically rebuild
the index. However, there are some things which even topen can't handle.
If topen cannot fix a damaged table, it will not open it, but return
an error code instead.  Your only alternative is to attempt to repair
the table with the trepair function.  Fixing damaged tables is
described in more detail in Chapter 15, Repairing Tables.


Closing a table

You should always close a table when you are finished with it. This will
insure that all unwritten data is written to disk and that any memory
that the table is holding is released.  You close a table with the
tclose function.  tclose is declared in crde.h as

    int tclose(TABLE *t);




                     - 19 -

For example, to close the above table, you would issue a

    tclose(t);


Checking a table's accessability

You can check if a table exists on disk with the taccess function.
taccess is declared in crde.h as

    int taccess(char *name, int mode);

name must be a valid table name.  You can check is a table exists by
calling taccess with mode set to 0.  taccess returns 0 if the table
exists or -1 if it does not.


Renaming tables

You can rename a table with the trename function.  trename renames the
table specified, including all indexes.  It is recommended that you use the
trename function whenever you want to rename a table.  trename is declared
in crde.h as

    int trename(char *old, char *new);

trename renames table old to new.  old  and new must both be valid table
names.  trename will not overwrite a table by the name new if one already
exists.  If one does, trename will not rename the table.


Erasing tables

You can erase a table with the terase function.  terase erases the
specified table and all associated indexes. It is recommended that you
always use terase to erase a table. terase is declared in crde.h as

    int terase(char *name);

name must be a valid table name.


Secondary Indexes

Creating Indexes

An index, often called a secondary index in CRDE, is a special type of
file which can help CRDE perform very fast searches.  An index in CRDE,
is very much like an index in a book.  While the subjects in a book is
not in any particular order, the index is always maintained in
alphabetic order.  Whenever you want to find a particular subject in
the book, you look up its reference in the index.  This is very
similar to how indexes are used by CRDE.  You can create a secondary
index on a table with the tindex function.  tindex is declared in
crde.h as

    int tindex(TABLE *t, char *id);

                     - 20 -

tindex returns 0 if the index was successfully created.


Index descriptors

id is a asciiz string which contains an index descriptor.  The index
descriptor is a list of all the columns which are part of the secondary
index.  Each column in the list must be separated by whitespace,
i.e. all characters classified by the standard library function
isspace() and the comma ','.  To create an index on the "sname" column
of the sups table would look like

    tindex(sups, "sname");

In addition, any columns in the index which a prepended with an
exclaimation point '!' are indexed in descending order.  Which means a
valid index on the "sname" column would also be

    tindex(sups, "!sname");

In general however, you should avoid using descending keys in indexes.
Since it doesn't matter to CRDE whether an index key is in ascending
or descending order, just like it doesn't matter whether a telephone
book is ordered from A-Z or Z-A.  There is no advantage to using one
over the other, except in some rare occasions when CRDE requires that
the index be read sequentially.  This only really occurs in the tsort
function.  Keeping you indexes in ascending order will help avoid
confusion.

You can also create compound indexes by including more than one column
in the index descriptor.  Secondary indexes may contain up to 8 columns.
For example, to create a compound index on the "city" and "sname"
columns

    tindex(sups, "city, sname")

You can think of compound indexes as being ordered like words in a
dictionary.  Words in a dictionary are ordered by their first letter,
and then their second, and then their third, etc.  Similarly, suppliers
in the above index are ordered by city and those in the same city are
ordered by their supplier name.  Thus this index would be useful in
searches for suppliers in a particular city, or those in a particular
city with a particular name.  It would not, however, be useful for
finding just a particular name, just as it would be fruitless to
search for a word in a dictionary without knowing its first letter.

Once you create the index, you never have to remind CRDE to update or
use it.  CRDE does it for you automatically.  The index is now part of
the table and is opened and closed along with the table.  The index
remains part of the table until such time as it is dropped specifically
by you.  The advantage of secondary indexes speed.  On large tables,
indexes are especially attractive because search times for exact values
can be reduced to less than a second in most cases.





                     - 21 -

However you should be sparing  when defining secondary indexes, only
using them when the increase in performance is significant enough to
warrant it.  While secondary indexes can increase search times, they
will slow other operations such as inserting, changing, and deleting
rows.  This is because CRDE must update the indexes whenever changes
are made to the table.  Secondary indexes also take up additional disk
space.  And sometimes creating a secondary index on a column(s) may
not always produce an increase in performance.  CRDE is exceptionally
fast at sequential searches.  Sometimes, on smaller tables, a
sequential search can be as fast as an indexed one.  CRDE may also
build a temporary index on its own when performing complex query
operations.  The best time to use a secondary index is on a large
table where you need to access exact values in a column.  For example,
you might want to place a secondary index on the "city" column if you
need to find a supplier who lives in Boston.   Poor choices for
secondary indexes are columns which contain lots of duplicate values,
f and $ columns, large c columns, columns which hold weights,
quantities, etc.


Primary index vs. Secondary Indexes

When you create a primary key, an index (the primary index) is created
which is similar to an index created by specifying those columns marked
with an asterisk '*' read from left to right in ascending order.  For
example, the primary index create on the orders table defined as

    orders = tcreat("orders", "*s# i, *p# i, qty i");

would create a primary index similar to a secondary index created by
the statement

    tindex(orders, "s#,p#");

For purposes of searching, sorting, and joining, etc. the two types of
indexes are functionally equivalent. Thus it would be redundant to
create the above secondary index on the orders table.

Despite the similarities, there are some basic differences between the
types of indexes:

    A primary index is created when the table is defined and cannot
    be removed afterwards.  Secondary indexes are created with the
    tindex function, and can be removed by tdropindex.

    A primary index cannot contain duplicate values, while a
    secondary index can.











                     - 22 -

Rebuilding indexes

You can also rebuild indexes on a table with the tindex function.
Simply specify the index to rebuild in the index descriptor.  Rebuilding
indexes is useful if you think that the index may be corrupt.  topen
will also rebuild an index automatically if it detects that the index
is corrupt.

    tindex(sups, "sname");  /* create index */
    /* ... */
    tindex(sups, "sname");  /* rebuilds index */


Dropping indexes

You can drop indexes from a table by using the tdropindex function.
tdropindex is declared in crde.h as

    int tdropindex(TABLE *t, char *id);

tdropindex will drop the index exactly matching the index descriptor
specified.  To drop the "sname" index from a table would look like

    tdropindex(sups, "sname");


Redundant indexes

It is possible when creating several secondary indexes, that two or
more indexes may be redundant.  An obvious example is shown above:
creating a secondary index identical to the primary index.  There
are other possibilities for redundancy.  CRDE prevents you from
creating duplicate secondary indexes because whenever you do so,
tindex simply rebuilds the original.  However, it does not prevent
you from creating redundant indexes.  You must do this on your own.

Redundant indexes are those which can be derived from, or used in
place by, existing indexes.  One example of a redundant index is
creating a secondary index identical to the primary index.  Here are
some other examples of redundant indexes:

    /* the second index is simply an inverse of the first */
    tindex("sname");
    tindex("!sname");

    /* the second index is simply an inverse of the first */
    tindex("s#, !city");
    tindex("!s#, city");

    /* the second two indexes are just more generals versions of the
    first */
    tindex("city,sname,status");
    tindex("city,sname");
    tindex("city");




                     - 23 -






Chapter 6

Viewing a Table



The tview function

CRDE provides an extremely powerful view facility which you can integrate
directly into your programs.  With a single function call you can

    - view any CRDE table in any text window on your monitor
    with your choice of color for text, frame, and background.  The
    table is automatically adjusted to the window and  you can
    scroll both horizontally and vertically through a table.

    - view only those columns you want to see, in any order you
    wish.

    - view the rows in any sorted order, or in the order they were
    originally inserted into the table.

    - create a pick list to select rows from a table.

CRDE's view function is called tview.  tview is declared in crde.h as

    int tview(TABLE *t, char *cl, char *id, int x1, int y1, int x2,
      int y1, int cattr, int fattr, int battr, void *rec);

t is the table to be viewed.  t must point to an open table.

cl is an asciiz string listing all the columns you want to view.  Each
column name must be separated by whitespace.  You may order the columns
in any order you wish.  If cl is NULL then every column will be
displayed in the order they appear in the table's table descriptor.
id is an index descriptor describing the order in which the rows in
the table will appear.  If id is NULL the rows will  appear in the
order they were originally inserted into the table.

x1,y1,x2,y2 are the coordinates of the window the view will appear in.
The coordinates can range from 1,1 to 80,25.  But the window must have
a minimum width of 5 and minimum height of 1.  Some columns, such as
large c columns, may be too wide to fit in the specified window.
If so, then the column will be truncated so that it fits.

cattr is the text attr for all values in the columns.

fattr is the text attr for the view frame.





                     - 24 -

battr is the text attr for any portion of the window not filled by the
view.

rec should point to a buffer large enough to hold one row in the table.
When you exit the tview function, the row that the cursor was last on
will be placed into rec.  You can use this feature to use tview as a
pick list to select a row from a table.  You can keep tview from
copying a row into the buffer by assigning rec to NULL.

tview offers a full range of movement up, down, left, right, beginning,
end. If the table is too large to fit in the specified window, tview
can scroll the table both horizontally and vertically so that you can
see every portion of the table.

    Cursor key          Action

    Up                   Move up a row.
    Down                 Move down a row.
    Left                 Move left one column.
    Right               Move right one column.
    PgUp                 Move up a page.
    PgDn                 Move down a page.
    Home                 Move to the first row.
    End                  Move to the last row.
    Ctrl-Left            Move to the first column.
    Ctrl-Right           Move to the last column.

You can exit from tview by pressing either the Enter key or the Esc key.
Which key you hit determines what value is returned by tview. This is
particularly valuable when using tview as a pick list.  You might use
tview to select a specific row then process the row if the user
presses Enter, or cancel the operation if Esc is pressed.

    Enter           Exit tview returning 13.
    Esc                Exit tview returning 27.

Selecting which columns you want to view can be an invaluable feature
when you want to restrict the information a particular user can see.
For example, you might want to let a user view an employees table
without letting them see the employee's salary.  tview always displays
the current row number in the leftmost column, regardless of which
columns you decide to display.

Selecting the sort order you want the table to be displayed in can be
invaluable as well.  However, when viewing very large tables (+5000
rows) in sorted order, tview can slow down dramtically.  Its usually
best to view large tables in the order in which they were inserted.


Limitations of tview

tview can only be used for viewing tables.  You cannot insert, update,
or delete rows from a table with tview.  tview adds about 10k to your
programs.




                     - 25 -






Chapter 7

The Search-Related Functions



The seven search-related functions

The CRDE library contains seven special functions classified as the
search-related functions.  These functions allow you to perform actions
on subsets of a table simply by specifying the rows that you want
to perform the operation on. The seven search-related functions are

    tselect         selects rows from a table
    tproject        projects a column from the table
    tdelete         deletes rows from a table
    tchange         changes rows in a table
    tscan           scans rows in a table
    tlookup        looks up a row in a table
    tget        gets rows from a table into a C array


The search expression

Every search related function expects a NULL terminated list of
arguments which formulate a search expression.  The search-related
function will only perform its operation on those rows which match
the criteria in the search expression.  Lets start with an example:
tselect.  tselect is declared in crde.h as

    TABLE *tselect(char *name, TABLE *t, ...);

Every search related function's parameter list ends with an
ellipsis(...).  A call to tselect will have the format

    answer = tselect(name, t, colname1, relop1, value1, colname2,
      relop2, value2, ..., NULL);

A search expression is made up of zero or more conditions followed by
a terminating NULL.  A condition is a colname[n], relop[n], value[n]
combination.

colname[n] must be a column name from table t.

relop[n] is one of six relational operators defined in crde.h.  Valid
relops are:







                     - 26 -

    EQ           equals, ==
    GT           greater than, >
    LT           less than, <
    LE, LTE       less than or equal to, <=
    GT, GTE       greater than or equal to, >=
    NE, NEQ       not equal to, !=

value[n] must be an argument which evaluates to a constant.  The type
of the argument must be identical to the type of colname[n] (c columns
expect a char * as a value).  For example, suppose you wanted a list
of all suppliers who live in Boston.  You could accomplish this with
the following call:

    answer = tselect("answer", sups, "city", EQ, "Boston", NULL);

tselect returns a table as a result.  The structure and primary key
of the created table are identical to the table being queried, in
this case sups.  tselect creates a table called answer identical in
structure to sups containing all the rows from sups where the "city"
column equals "Boston".  Notice the terminating NULL.  Every search
expression must end with a NULL.

A search expression may contain up to 16 conditions.  A search
expression may also contain no conditions.  In this case, the search
expression would only consist of the terminating NULL.  A search
expression with no conditions will select every row from the table.


The search engine

CRDE handles searches with its search engine.  The search engine
parses the search expression and determines the fastest way to perform
the search, regardless of how the search expression is specified.
The search engine knows about your indexes and will use them
automatically to assist in the search.


Search-related operations

Selecting rows

tselect is one of several functions which returns a table as a result.
tselect returns a table identical in structure as t, containing all
the rows from t matching the search expression.

    answer = tselect("answer", sups, "city", EQ, "Boston", NULL);

answer contains all the rows from sups where the supplier lives in
Boston.  The structure of the answer table is identical to sups.
It has the same columns and column types.  It also has the same
primary key.  It will not have any secondary keys,  even if sups
does.  Although CRDE considers secondary keys to be part of a table,
they are not considered part of the table's structure.





                     - 27 -

Projecting rows

Projecting columns from a table is similar to selecting them, except
you have the option to choose only those columns you want to appear
in the answer table.  You can project columns from a table with the
tproject function.  tproject is declared in crde.h as

    TABLE *tproject(char *name, TABLE *t, char *pl, ...);

The pl parameter is an asciiz string containing a list of those columns
you want to appear in the resulting table.  You can select only those
columns you want, in any order that you want. For example, to select
only a supplier's name and city from the sups table would look like

    answer = tproject("answer", sups, "name,city", NULL);

Notice there are no conditions in the search expression, just the
terminating NULL.  A search expression with no conditions selects
every row in the table.  If you wanted the city column to appear
first, just switch them around

    answer = tproject("answer", sups, "city,name", NULL);

Of course, you could also project the name and city columns from only
those suppliers in Boston with

    answer = tproject("answer", sups, "name,city", "city", EQ, "Boston",
      NULL);

The structure of the new table will be identical to the columns listed
in pl in the same order they appear in pl. The type of each column is
identical to its type in t.


Defining a primary key with tproject

The above query create table a table with two columns which has a
structure identical to "name c26, city c26".  The table has no
primary key.  You can specify a primary key on the projected table
by prepending any columns with an asterisk '*'.  Like in a table
descriptor, a maximum of 8 columns may be part of the primary key.
The projected table by default does not inherit a primary key unless
it is specifically specified.  For example, if you projected the "s#"
column from sups, the resulting table would not inherit the primary
key of "s#" unless you specifically used "*s#".  Defining a primary
key on the projected table can very useful, especially in removing
duplicate values from the result table.  For example suppose you
wanted a list of all cities where there are suppliers.  Defining a
query like

    answer = tproject("answer", sups, "city", NULL);







                     - 28 -

might not have the exact effect you would like.  Why? because if more
than one supplier lived in the same city, that city would appear more
than once in the list.  If more than one supplier lives in the same
city, you only want that city to appear once.

You can get the result you want by defining a primary key on the
"city" column.

    answer = tproject("answer", sups, "*city", NULL);

The resulting table will have a primary key on the "city" column.
Because, by definition, no two rows in a table may have the same
key values, CRDE will automatically filter out any duplicates.
Similarly, tproject can be used to change or remove the primary key
on a table.  For example, if we wanted to remove the primary key from
the sups table you could do this with

    answer = tproject("answer", sups, "s#,sname,city,status", NULL);

answer would be a copy of sups without a primary key.


Deleting rows

You can delete rows from a table with the tdelete function.  tdelete
is declared in crde.h as

    long tdelete(t, ...);

tdelete is fairly straightforward.  All rows matching the search
expression are deleted from the table.  For example, to delete
supplier #5 from the sups table

    tdelete(sups, "s#", EQ, 5, NULL);

tdelete returns the number of rows deleted.


How rows are deleted

tdelete doesn't physically delete rows from a table.  Rather it marks
the space as unused.  The space is reused when you re-insert rows into
the table.  Because CRDE reuses the space in tables caused whenever a
deletion occurs, you do not have to "pack" your tables like you do in
other data managers.  A special case is when you want to remove all
rows from a table.  This is usually referred to as emptying a table.
You can empty a table with tdelete by issuing a

    tdelete(t, NULL);

However, this would leave the entire table unused and waste a lot of
disk space.  CRDE provides a function to remove all the rows from a
table, which does physically reclaim any unused space, called tempty.
tempty is declared in crde.h as

    tempty(TABLE *t);


                     - 29 -

You should always use tempty when you want to remove every row from a
table for this reason.  Using tempty is also much quicker than the
tdelete method.


Changing rows

You can change values in a table by using the tchange function.  The
tchange function is declared in crde.h as

    long tchange(TABLE *t, ...);

tchange returns the number of rows changed.  tchange is little
different from the other search-related functions.  It expects not one,
but two NULL terminated lists of arguments.  The first is a list of
changes, followed by the standard search expression.  A sample call
to tchange will have the format

    tchange(t, chcol1,chval1, chcol2, chval2, ..., NULL, colname1,
      relop1, value1, colname2, relop2, value2, ..., NULL);

A change list is made up to 16 changes.  A change consists of a
colname[n], chcol[n] combination.

chcol[n] is the column name of the column to be changed.

chval[n] is the value that the column will be set to in every row which
matches the search expression.  chval[n] must resolve  can be a
constant, variable or function result which has a type identical to
chcol[n]. c columns expect a type char *.

For example, suppose you want to change part #3's color to purple.
You could do this with a simple tchange call

    tchange(parts, "color", "purple", NULL, "p#", EQ, 3, NULL);

tchange can be used to make global changes.  For example, at the end
of a year you might want to reset every supplier's status to 0. To do
this with tchange would look like

    tchange(sups, "status", 0, NULL, NULL);

A common error made with tchange is that you must remember to specify
two NULLs instead of just one.


Scanning rows

You can count the number of rows which match a search expression by
using the tscan function.  tscan is declared in crde.h as

    long tscan(TABLE *t, ...);

tscan returns the number of rows scanned.




                     - 30 -

tscan really doesn't do anything accept count the number of rows which
match the search expression.  tscan is not usually used much in
applications.


Looking up a row

You can determine if a row matching a given search expression exists
with the tlookup function.  The tlookup function is declared in crde.h
as

    int tlookup(TABLE *t, ...);

tlookup returns 1 if a row matching the search expression was found or
0 if it was not.  tlookup stops searching after the first match is found.


Getting rows

tget is similar to tselect, except that the rows that are selected are
placed in a C array rather than a table.  tget is declared in crde.h as

    long tget(TABLE *t, void *array, long n, ...);

tget places up to n rows that match the search expression into the
buffer pointed to by array.  The buffer should be an array of at least
n structures identical the structure of the table t.  It is important
to remember that the rows that are extracted with tget are only copies
of the rows in the table.  The rows retrieved by tget are not removed
from the table, nor will changing the rows in the array have any affect
on the original values in the table.

The following exmaple places every supplier with a status > 5 into an
array buf.


    typedef struct {
        int s;
        char sname[26];
        char city[26];
        int status;
    } sup;

    sup buf[20];
    int i;

    i = tget(sups,  buf, 20, "status", GT, 5, NULL);

tget returns the number of rows actually retreived, which in this case
will be between 0 and 20.

tget is often used in conjunction with treplace.  You can get rows
from a table with tget, perform operations on them, and return them to
the table with treplace.




                     - 31 -

"..If" functions

Although a search expression is capable of handling many searches, it
cannot handle all of them.  Fortunately, CRDE provides a version of each
of the above functions which allow unlimited searching capabilities.
These are referred to as "..if" functions.  They are, respectively

    tselectif
    tprojectif
    tdeleteif
    tchangeif
    tscanif
    tlookupif
    tgetif

Each is identical in use and declaration to its brother function in
every way except one.  Each excepts an additional parameter which can
be used as a filter to create user-defined search conditions.  In each
declaration, the user-defined function action, appears directly before
the ellipsis in a search related function.  For example, tselectif
is declared as

    TABLE *tselectif(char *name, TABLE *t, int (*action)(void *), ...);

action is a user-defined function which must accept a void * as a
parameter and return an int.  Every row which matches the search
expression is passed to action (which is referenced by void *) for
further evaluation.  The result of action determines whether or not
an operation will occur.

    Result of action           What happens

        > 0              Row is selected.

        = 0              Row is not selected.

        < 0              The function automatically aborts as
                if an error occurred.  If the "..if"
                function returns a TABLE * then it will
                return NULL otherwise the result of
                action is returned by the function.
                terrno is set to the result of action.

By using "..if" functions, programs can create virtually any search
criteria.  For example, suppose you wanted to select all suppliers
who either live in Boston or Denver.  You cannot create this sort of
search expression because only those rows which match every condition
in the expression are selected.   Specifying

    answer = tselect("answer", sups, "city", EQ, "Boston", "city",
      EQ, "Denver");

would not answer the question, because CRDE would try to select every
supplier would lives in Boston and Denver, an impossibility.  This
problem however, is easily handled with a simple "if" function.



                     - 32 -

Example #1.

    typedef struct {
        int no;
        char sname[26];
        char city[26];
        int status;
    } sup;

    int iffunc(void *rec)  {
      return strcmp(((sup *)rec)->city, "Boston") == 0 ||
        strcmp(((sup*)rec)->city, "Denver") == 0;
    }

    answer = tselectif("answer", sups, iffunc, NULL);

No search expression is supplied, so tselectif passes every row in the
table to iffunc for evaluation.  iffunc returns true if the city field
equals Boston or Denver.  Notice that since rec must be a void *, you
must typecast it to extract data from the row.


Example #2.

Select all parts which are red ignoring any case differences.

    int ColorIsRed(void *rec)  {
        return stricmp(((part *)rec)->color, "RED");
    }

    answer = tselectif("answer", parts, ColorIsRed, NULL);


Example #3.

Change all the part's colors to uppercase.

The user-defined function for tchangeif works a little differently
than in other "..if" functions.  Any changes made to the row passed
to action are reflected in the table (if, of course, action returns > 0).
Thus tchangeif allows you to perform user-defined changes as well as
user-defined selection.  For example, to change every part's color to
uppercase would look something like

    int ColorUpr(void *rec)  {
        strupr(((part *)rec)->color));
        return 1; /* change every row */
    }

    tchangeif(parts, CityUpr, NULL, NULL);








                     - 33 -

The NULLs are required even when no change list or search expression
is specified.  Note:  any changes made in the change list are made after
the row is passed to action.  action may modify the record passed to
it without harm (although this does have side effects when used in
tchangeif).  action can perform operations on other tables based on
the information passed to it.  action can even perform queries on the
host table (the table whose rows are being passed to action).  This
allows for extremely powerful and complex querying capabilities.
However, there is one restriction.  action is disallowed from altering
t in any way.  When any "..if function" calls action, t is automatically
placed in a special read-only mode.  Because of the complex nature in
which CRDE performs searches, t cannot be changed during the course of
a query.  CRDE functions which attempt to alter t will return a -152,
or "table may not be modified", error.  CRDE functions which can
change a table are listed below:

    tadd        tchange        tchangeif    tchcol
    tdelete        tdeleteif     tdropindex     tempty
    tindex         tinsert        tload          treplace
       tsubtract      tupdate

In addition, you may not close or drop table in read-only mode.  This
is also the only case in which if t is a TEMP table, it will not be
automatically dropped by any CRDE functions called in action (TEMP
tables and auto-cleanup are described in full detail in Chapter 10).

The following example, although meaningless, demonstrates the abilities
and limitations within an if function.

    TABLE *sups, *answer;

    int  iffunc(void *rec)
    {
      TABLE *t;
      double d;

      /* these function calls are ok, do not change sups */
      t = tselect("test", sups, "s#", EQ, 100, NULL);
      tdrop(t);
      taverage(sups, "status", &d);

      /* illegal to change sups within iffunc */
      t = tdelete(sups, "status", GT, 20, NULL);

      /* cannot close sups within iffunc */
      tclose(sups);
      return 1;
    }

    answer = tselectif("answer", t, iffunc, NULL);








                     - 34 -

As you can see, "..if" functions provide almost unlimited querying
capabilities.  You might think the these functions could replace
their regular counter parts because you can do everything a search
expression can do inside of action.  However, there are several
reasons why not to:

    - the most obvious is that "..if" functions require you to write
    a user-defined function for every query.

    - CRDE can only take advantage your indexes if you specify a
    search expression. For this reason you should always put as
    much of the query    in the search expression as you possibly
    can.

    - and since action must receive a copy of every record for
    evaluation, "..if functions" are slightly slower than their
    regular counterparts.

Some queries, however, cannot be performed without an "..if" function.
The full capabilities of "..if" functions are demonstrated in Chapter
11, Performing Queries.





































                     - 35 -






Chapter 8

Miscelleneous Operations



Restructuring tables

Like most data management systems (good ones anyway), CRDE provides
a way to restructure tables, without having to unload and reload all
the table's information.

Unlike other libraries which require you to use a special utility to
restructure a table, CRDE provides the trestruct function.  With the
trestruct function you can

    - add columns to a table
    - remove columns from a table
    - reorder columns in a table
    - change column types and sizes
    - change the primary key

trestruct is declared in crde.h as

    TABLE *trestruct(char *name, TABLE *t, char *td);

trestruct returns a restructed version of t based upon td.  td should
be a table descriptor like the one described in Chapter 3.  trestruct
interprets the columns in td as follows:

    - If the column name does not appear in t then it is added to
      the table and filled with some default value (like 0 or "")
      depending upon the datatype.

    - If the column does appear in t and has the same type, then all
      data from the original column is copied into the new column.

    - If the column appears in t but has a different type, trestruct
      converts all data in the original column to the type of the new
      column.

    - Any columns in the original table t, which do not appear in
      td are not included in the restructured table.










                     - 36 -

Data conversion

trestruct can convert any data type to any other data type except for
dates.  Date columns can only be converted to char columns and only
char columns can be converted into dates.  When converting data to
char columns, trestruct converts the value  into a string and copying
it into the new column.  f columns will have 4 decimal places, $
will have 2. Dates are formatted as  "mm/dd/yyyy".  If the string is
too large to fit in the new column  it will be truncated automatically.
When converting char types to other data types, the char column must
hold be valid representation of the new value.  If it isn't then the
column will be initialized as described above.  Data converted to f
and $ columns may contain any number of decimal places.  Data
converted to dates must have the format "mm/dd/yyyy".  If a value
being converted to a numeric data type ( i, l, f, $ )  is out of
range for that type, the converted value is undefined.

For following example uses trestruct to create a new sups table:

    t = trestruct("restruct", sups, "s# l, sname c16, city c16,"
      "first d");

    - "s#" is converted to type l.
    - "sname" and "city" are shortened.
    - "status" does not appear in the table descriptor and so is
      not included in the new table.
    - The "first" column is added to the table.  It contains the
      date the first order was placed to that supplier.

trestruct returns a restructed copy of the sups table.  sups is still
intact.  To replace sups with the new table, you would have to erase
the old sups and replace it with the new table.

    tdrop(sups);
    tclose(t);
    trename("restruct", "sups");


Renaming columns

You can change a column's name with the tchcol function.  tchcol is
declared in crde.h as

    int tchcol(TABLE *t, char *old, char *new);

tchcol renames column old to new.  The type or size of the column remains
unchanged.











                     - 37 -

Getting info from your tables

CRDE provides several functions for getting information about the
structure and properties of a table.

    trows        Returns the number of rows in a table.
    tcols           Returns the number of columns in a table.
    tcoltype       Returns the type of a column.
    tcolsize       Returns the size of a column, in bytes.
    trowsize      Returns the size of a row, in bytes.
    tindexes       Returns the number of secondary indexes on a
            table.
    tkeyed         Returns 1 if the table has a primary key or 0
            if not.

    tstruct       Returns a table containing information about the
            structure of a table.

    tkeys          Returns a table containing information about the
            secondary indexes on a table.

Of these, only tstruct and tkeys bear much detail.  The others are
relatively self-explanatory.  tstruct is declared in crde.h as

    TABLE *tstruct(char *name, TABLE *t);

tstruct returns the structure of a table, as a table.  The result is a
table which is equivalent to

    tcreat(name, "name c18,type c6").

Column names and types appear in the struct table in the order that
they were declared.  Columns which are part of the primary key have
their names prepended with an asterisk '*'.  Using tstruct on the sups
table would produce the following table:

    name             type
    -----------------------    ------
    *s#            i
    sname            c26
    city            c26
    status            i


Altering the table produced by tstruct has absolutely no effect upon the
structure of the table passed to tstruct.

tkeys is a similar function for obtaining information about a table'
s secondary indexes.  tkeys produces a table equivalent to

    tcreat(name,"ext c3, key c144").







                     - 38 -

The ext column contain the indexes' extension on disk,  "s0" - "s7".
The key column contains the index descriptor which created the
secondary index.  To illustrate, suppose the sups table had a two
secondary indexes created by

    tindex(sups, "sname");
    tindex(sups, "city,status");

Calling tkeys on sups would return the table

    ext    key
    -------    -------------------------
    s0    sname
    s1    city,status


Importing and Exporting data

CRDE provides functions for importing and exporting data to other
file formats.  CRDE currently supports the ascii and dBASE file formats.
Since most other data managers support either one or both of these
types, you should have no problem converting CRDE tables to and from
virtually any other format.


Ascii files.

CRDE supports comma-delimited ascii compatible files.  CRDE can import
an ascii file with its timportascii function.  timportascii is
declared in crde.h as

    TABLE *timportascii(char *name, char *td, char *source);

name is the name of the table which will be created.  source is the
complete pathname (including extension) of the ascii file to be
imported.  td is a table descriptor like the one described in Chapter
3, and may include a primary key.  It should describe what the
resulting table will look like.  This is necessary because an ascii
file contains no details about the types or sizes of the columns it
contains.

timportascii requires that each column in a row be separated by a
comma, and that each row exist on a single line of the test file.
timportascii reads data from the ascii file according to the table
descriptor you define.  The data in the ascii file must be formatted
accordingly for each type of CRDE column.

CRDE
type    Expected format of column in ascii file

c     A character string contained in quotes "".  If the string is
    too large for the column, it will be truncated to fit.

i          A base 10 integer is expected. Negative values should be
    preceded with a negative sign -.  If the integer is out of
    range for the column, the value for that column is undefined.


                     - 39 -

l          A base 10 integer is expected.  Negative values should be
    preceded with a negative sign -.  If the integer is out of
    range for the column, the value for that column is undefined.

d          A date in the format of mm/dd/yyyy is expected. The date must
    be enclosed in quotes.  Whitespace is ignored.

f         A floating point value with any number of decimal places.
    Exponential notation may be used, +/-eNN. Negative numbers
    must be preceded with a negative sign -.  If the number is
    out of range, the resulting value imported is undefined.

$          Same as f.


Any whitespace between columns, and rows, is ignored by timportascii.

You can export a CRDE table to an ascii file with the texportascii
function.  texportascii is declared in crde.h as

    long texportascii(TABLE *t, char *dest);

t is the table to be exported.  dest if the filename of the ascii file
to create.  Every column in the file will be separated by a comma, and
each row will appear on an individual line.  Output is formatted
depending upon datatype.

CRDE
type    Format of column in ascii file

c          CRDE will output a character string enclosed in quotes "".

i          CRDE will output a base 10 integer. Negative numbers will be
    preceded with a negative sign -.

l          CRDE will output a base 10 integer. Negative numbers will be
    preceded with a negative sign -.

d          Dates are output in the format mm/dd/yyyy and enclosed in quotes.

f          Floating point numbers are output with 4 decimal places.
    Negative number are preceded with a negative sign.

$          Dollar values are output with 2 decimal places. Negative numbers
    are preceded with a negative sign.


texportascii returns the number of rows successfully exported.










                     - 40 -

dBASE files

CRDE supports the dBASE III and dBASE III Plus file formats.  You can
import a dBASE file into a CRDE table with the timportdBASE function.
timportdBASE is declared in crde.h as

    TABLE *timportdBASE(char *name, char *source);

Importing a dBASE file is somewhat simpler than importing an ascii file
since no column information is required.  timportdBASE automatically
reads the dBASE file header for information about field types and
lengths and creates the CRDE table structure for you.  When converting
dBASE field types to CRDE column types, CRDE uses the smallest CRDE
column type which can correctly hold all legal values for the dBASE
field.  timportdBASE make the following translations when converting
a dBASE file to a CRDE table.

dBASE
type    How dBASE field is converted to CRDE

C          c column equaling length of dBASE field + 1.

D          dBASE's dates are converted to CRDE's date format.

N         If the number of decimal places equals 2 then the colum type
    is $.  If decimal places is > 0 then column is type f.  If the
    length of the number field is > 11 then the column is type f.
    If the length of the number field is < 7 then the column is
    type i.  Otherwise the column is type l.

L          Logical fields are converted to type i. If the field is 'Y' or
    'T' the column will have a value of 1, otherwise 0.

M          CRDE does import memo fields.  Any memo fields in the dBASE
    file are ignored.


You can export to a dBASE file with the texportdBASE function.
texportdBASE is declared in crde.h as

    long texportdBASE(TABLE *t, char *dest);

texportdBASE creates the dBASE header for you by converting CRDE
columns to uppercase and creating dBASE field types which are
equivelent to each CRDE column.  texportdBASE make the following
translations when converting CRDE tables to the dBASE format.












                     - 41 -

CRDE
type    How CRDE column is converted to dBASE

c          Character field with a length equaling the length of the c
    column - 1.

i          Numeric field with a length of 6 and 0 decimal places.

l          Numeric field with a length of 11 and 0 decimal places.

d          CRDE dates are converted to dBASE format.

f          Numeric field with a length of 19 and 4 decimal places.

$          Numeric field with a length of 19 and 2 decimal places.











































                     - 42 -






Chapter 9

Working with Dates



The date_t type and date math

CRDE introduces a new date type, date_t, and 15 functions to create
and manipulate dates.

d columns in CRDE tables are of type date_t.  date_t is declared in
date.h as

    typedef long date_h;

CRDE represents dates by assigning a date as the number of days since
1/1/0001.  The advantage of storing dates this way is that you don't
need to resort to special functions to add, subtract or compare dates
because you can use C's built in mathmatical abilities.  For example,
to add 7 days to a date you would simply add 7 to the date_t value

     d = d + 7;

To find the difference between 2 dates, you simply subtract them.

    dif = d2 - d1;

You can compare dates just like any other integer types. To test if
two dates are equal, use then == operator.

    if (d1 == d2) { /* ... */ }

You can just as easily use the >, < >=, <= and != operator when
comparing dates.

Although storing dates as an integer makes them easy to work with in
a mathematical sense, its not very meaningful to say that you received
an order 729172 days after 0 a.d.  Thus CRDE's date functions provide
a way to easily convert dates to/from standard calender notation.


Converting calender dates

The mkdate function is used to convert a calender date into the date_t
type.  mkdate is declared in date.h as

    date_t mkdate(int month, int day, int year);





                     - 43 -

To convert the date 5/6/1968 to date_t would be

    date_t d;

    d = mkdate(5, 6, 1968);

You must remember to use the century in front of the year.  A date like

    d = mkdate(5, 6, 68);

would return a valid date_t date, for May 5, 0068, not May 5 1968!

mkdate knows about calender months, leap years, and leap centuries.
It will return -1 if the date passed to it is invalid:  like 2/29/1990.

Similarly, you can convert a date_t type to a calender date with the
gmdate function.  gmdate is declared in date.h as

    int gmdate(date_t d, int *month, int *day, int *year);

To extract the month, day and year from a date_t type would look
something like

    int month, day, year;

    gmdate(d, &month, &day, &year);

You can extract the day, month, and year individually from a date_t
with the day, month, and year functions respectively:

    int day(date_t d);
    int month(date_t d);
    int year(date_t d);

Example:

      d = mkdate(11, 2, 1986);
    printf("%d/%d/%d", month(d), day(d), year(d));

The output to the above printf function is

         11/2/1986


Miscellaneous date functions

Not all of CRDE's date functions work directly with the date_t type.
To determine if a year is a leap year, you can use the leapyear
function.  A related function, daysinyear, returns the number of days
in a given year.

    int leapyear(int year);
    int daysinyear(int year);





                     - 44 -

To find how many days have past since the beginning of the year, you
can use the dayofyear function.  dayofyear is declared in date.h as

    int dayofyear(int month, int day, int year);

dayofyear returns the number of days past for that date of that year.
dayofyear has a complement function which takes the number of days and
returns the appropriate month and day.  monthday is declared as

    int monthday(int days, int year, int *month, int *day);

Given the year and total number of days past, monthday can determine the
month and day.


Month and year math

Most data managers allow you to add and subtract days from dates.  But
few offer the ability to add by months or years.  Adding by months or
years is complicated by the fact that all months and years do not
contain the same number of days.  A working, but hardly adequate,
substitute used by some DBMS's is to add and subtract multiples of
30 when adding months or multiples of 365 when adding years.  But
suppose you wanted to collect a payment one month from July 1, 1990?
Adding 30 days would result in July 31, 1990, not August 1, 1990 as
expected.

CRDE provides functions for adding both months and years to a date.
They are declared in date.h as

    date_t monthadd(date_t d, int months);
    date_t yearadd(date_t d, int years);

The monthadd function accepts a date and returns a date with months
months added to it.  monthadd correctly adds months.  Adding 1 month
to July 1, 1990 yields August 1, 1990.  You can subtract months from
a date by simply making the months parameter negative.  yearadd works
in a similar fashion.  yearadd returns a date +/- the number of years
specified.


Day of week functions

Often times when using dates, the day of week can be significant.
For example, you might want to only allow orders to be shipped during
the week, and not on the weekend.  CRDE provides two useful functions
which use the day of week.  dayofweek returns the day of week of the
date specified.  dayofweek is declared in date.h as

    int dayofweek(date_t);

dayofweek returns a constant between 0 and 6 which stand for the days
of the week between Sunday and Saturday.  To help the translation, has
constants declared in date.h for every day of the week.




                     - 45 -

    #define SUNDAY        0
    #define MONDAY        1
    #define TUESDAY     2
    #define WEDNESDAY    3
    #define THURSDAY    4
    #define FRIDAY          5
    #define SATURDAY    6

firstday is a useful function to determine the date of the first
Monday, Tuesday, Wednesday, etc. of the month.  firstday  is declared
in date.h as

    date_t firstday(int month, int year, int dayofweek);

Suppose you wanted an order to be placed on the first Monday of
January,1990.  You could easily accomplish this with firstday

    d = firstday(1, 1990, MONDAY);

firstday returns the date as a date_t type.  MON is a constant for a
day of the week as described above.  firstday is useful in many
applications.  You can also use firstday for calculating the second,
third, and fourth Monday, Tuesday, etc. of a month by simply
calculating the first day and adding the appropriate number of weeks.
For example, the following function returns the date of the fourth
thursday of any month of any year:

    date_t FourthThu(int month, int year)  {
      int i;

      i = firstday(month, year, THURSDAY);
      return i >= 0 ? i + 21 : i;
    }

























                     - 46 -






Chapter 10

Temporary Tables



STATIC tables

The concept of a temporary table is very important in CRDE.  Unlike
in other RDBMS's temporary tables have special meaning in CRDE.
Temporary tables can be useful in a number of situations,
especially in queries.

In CRDE you can specifically designate a table as temporary.  There
are actually two types of temporary tables in CRDE, the first of
which is STATIC.  To specify a table as a temporary table, simply
use STATIC instead of a regular table name like "sups".  For example:

    answer = tcreat(STATIC, "*s# i, sname c26, city c26, status i");

STATIC is defined in crde.h.  You can use STATIC wherever you would
use a normal table name.

    t = tcreat(STATIC, "*s# i, sname c26, city c26, status i");
    t = tselect(STATIC, sups, "city", EQ, "Boston", NULL);

Using STATIC relieves the programmer from having to give temporary
tables some unique name.  CRDE automatically generates a unqiue name
for the table which is not seen by the user.  Temporary tables can be
distinguished by TABLE * each is assigned to.  You may open any number
temporary tables, as long as the total number of tables does not
exceed 128.


Virtual tables

Another advantage of CRDE's temporary tables is that CRDE can create
and maintain them completely in memory.  This has several advantages,
the greatest of which is speed.  The overhead of creating a file(s)
for the table is removed, which also means that no file handles are
used by the temporary table.  All reads and writes to the table go
directly to memory so any operations performed on it are extremely
fast.  Sometimes a temporary table may be too big to fit in memory, or
it might be hogging so much memory that CRDE cannot perform its other
operations efficiently. When this happens, CRDE will place part or all
of the table on disk to release memory.   This technique is called
transparent migration.  CRDE will assign the table a unique table name
and flush it to disk.  The important thing is that CRDE handles all the
details in managing temporary tables for you.  You never need to worry
about whether a temporary table is on disk, in memory, or both.  You
don't even have to worry cleaning up afterwards.  When you close the
temporary table, the image on disk (if any) is removed automatically.

                     - 47 -

Temporary files created by CRDE will have the format "~TMPXXXX " where
XXXX is a hexadecimal number ranging from 0x0 to 0xFFFF.  Normally,
these files are deleted automatically for you by CRDE.  Sometimes,
however, you may find one or more of the lying around on your disk.
This can happen is you forget to close a temporary table or your
program terminates abnormally.  You can erase these files since they
are no longer used by CRDE.

Despite all these special abilities, CRDE places no restrictions on
what you can do with temporary tables.  You can use a temporary table
any place you would use a regular one. You can query them, update them,
create indexes on them, and even perform transactions on them.


TEMP tables and automatic cleanup

The second type of temporary table is the TEMP table.  TEMP tables are
identical to STATIC tables in every way except one.  TEMP tables support
a feature called auto-cleanup, which means if you use it correctly, you
don't even need to specifically close the table when your through with
it.  CRDE will know when you are through with it and will close (and
delete) it for you automatically.

When and how would you want to use this feature?  Well maybe an example
would better illustrate the point.  Suppose you use tselect to select
all the rows from the sups table where city EQ "Boston"?  You also
want it sorted by the supplier's name.

    t = tselect("temp1", sups, "city", EQ, "Boston", NULL);
    r = tsort("temp2", t, "sname");
    tdrop(t);

Notice how this requires three steps.  Notice how you must remember
to drop t.  Otherwise it would just lie around wasting memory. Now
lets try declaring t as a TEMP table.

    t = tselect(TEMP, sups, "city", EQ, "Boston", NULL);
    r = tsort("temp", t, "sname");

Notice the tdrop(t) statement is gone.  Because t is TEMP, it was
automatically dropped by tsort for you.

But here's where the real advantage of TEMP tables shines.  We can
save another step by nesting the tselect statement within tsort!

    r = tsort(STATIC,
        tselect(TEMP, sups, "city", EQ, "Boston", NULL),
        "sname"
    );

There is no limit to the number of TEMP tables which may be nested
in this manner.






                     - 48 -

What happens if you nest a table which is not a TEMP table?  Well
since its not a TEMP table it won't be automatically dropped by the
function which receives it.  And because you have no access to it
(its not assigned to any variable), it will simply lie around for
the duration of your program, using up memory.  Eventually, CRDE will
realize that the table is not being used and will release as much
memory from the table as it possibly can. However, there will always
be a small piece of the table (usually about 1k) which will remain in
memory until the program terminates.


Rules for using TEMP tables

Which functions support auto-cleanup of TEMP tables?  Practically all
of them.  You can determine if a table will be automatically dropped
by a CRDE function by following this simple rule:

    A CRDE function will automatically drop a TEMP table passed
    to it as a parameter if the function does not or can not
    alter the table in any way.  If the TEMP table cannot be thus
    affected by the function, it will be automatically dropped when
    the function completes, even in the event of an error.

Altering a table means inserting, changing, or deleting rows or columns
in the table.  Here is a list of all CRDE functions which can alter
tables:

    tadd        tchange        tchangeif      tchcol
    tdelete        tdeleteif        tdropindex     tempty
        tindex         tinsert        tload          treplace
       tsubtract      tupdate

For every rule, there are always exceptions.  Some CRDE functions will
not automatically drop a TEMP table, regardless of whether they alter
it.  These functions are generally those ones related to transaction
tracking and memory management.

    tcommit        tflush         tnormal        tmark
     tmode          trelease        trollback      ttransact
     twritec

If a CRDE function accepts two tables as parameters, it will drop
either of them if they are TEMP.  However, a TEMP table in read-only
mode will not be dropped automatically by any CRDE function.  This can
only occur if the TEMP table is used in one of CRDE's "..if" functions.
The table will not be automatically dropped by any CRDE function calls
in the user-defined function specified in the "..if" function.

You will find using both TEMP and STATIC tables invaluable in your
applications.  Use TEMP whenever you nest a CRDE function which returns
a table, inside another CRDE function, otherwise use STATIC.







                     - 49 -






Chapter 11

Performing Queries



What is a query?

A query is a question that you ask about the information in your
database.  You might want to find out which suppliers are supplying
a certain part, or which how many red parts are currently on order,
or how many suppliers supply both bolts and nuts, etc.  Queries have
many uses in applications. Queries are usually used to answer questions,
create lookup or view tables, make calculations, or generate reports.

CRDE contains very powerful querying capabilities, matching those found
in high level querying languages such as SQL.  Because it is rooted in
a procedural language however, CRDE has abilities which range far
beyond the capabilities of other relational languages.  CRDE provides
the tools to create queries as complex as you could possibly imagine.

Developing queries in any relational language is an art form of sorts,
and doing so in CRDE is no exception.  This chapter attempts to look
at many different types of queries, and CRDE's approach to each.  To
assist you, each query is first described in English.  The CRDE answer
of the query will appear next followed by an equivalent SQL query.
This will help you more quickly understand the nuances of how to create
CRDE queries.  The answer to each query is also shown.

Each of the following queries are just small portions of code and not
meant to be entire programs.  All assume that the following declarations
have been made:

    TABLE *sups, *parts, *orders, *answer, *set;

    typedef struct {
        int s;
        char sname[26];
        char city[16];
        int status;
    } sup;

    typedef struct {
        int p;
        char pname[16];
        char color[11];
    } part;






                     - 50 -

    typedef struct {
        int s;
        int p;
        int qty;
    } order;

    sups = tcreat(STATIC, "*s# i, sname c26, city, c16, status i");

    tload(sups, 1, "Smith, John", "Boston", 5);
    tload(sups, 2, "Jones, Ben", "New York", 10);
    tload(sups, 3, "Barnes, Frank", "Boston", 15);
    tload(sups, 4, "Landon, Mark", "Auburn Hills", 20);
    tload(sups, 5, "Andrews, Jim", "New York", 5);

    parts = tcreat(STATIC, "*p# i, pname c16, color c11");
    tload(parts, 1, "bolt", "red");
    tload(parts, 2, "screw", "red");
    tload(parts, 3, "screw", "blue");
    tload(parts, 4, "nut", "gray");
    tload(parts, 5, "bolt", "gray");
    tload(parts, 6, "nail", "red");

    orders = tcreat(STATIC, "*s# i, *p# i, qty i");
    tload(orders, 1, 1, 100);
    tload(orders, 1, 2, 200);
    tload(orders, 1, 3, 300);
    tload(orders, 1, 4, 400);
    tload(orders, 1, 5, 500);
    tload(orders, 1, 6, 600);
    tload(orders, 2, 1, 100);
    tload(orders, 2, 2, 100);
    tload(orders, 2, 6, 100);
    tload(orders, 3, 1, 200);
    tload(orders, 3, 2, 50);
    tload(orders, 4, 3, 100);
    tload(orders, 4, 5, 500);

/*
sups

    s#     sname        city        status
    -------    ---------------    ---------------    -------
    1    Smith, John    Boston        5
    2    Jones, Ben    New York    10
    3    Barnes, Frank    Boston        15
    4    Landon, Mark    Auburn        20
    5    Andrews, Jim    New York    5











                     - 51 -

parts

    p#    pname        color
    -------    ---------------    -----------
    1    bolt        red
    2    screw        red
    3    screw        blue
    4    nut        gray
    5    bolt        gray
    6    nail        red

orders

    s#    p#    qty
    -------    -------    -------
    1    1    100
    1    2    200
    1    3    300
    1    4    400
    1    5    500
    1    6    600
    2    1    100
    2    2    100
    2    6    100
    3    1    200
    3    2    50
    4    3    100
    4    5    500
*/



Simple Queries

Simple Select Queries

The majority of queries will involve selecting rows from a single
table based on one or more conditions.  These types of queries can
be answered with the tselect function.  tselect is very much like
using an SQL SELECT statement with the * operator.  tselect, however,
is limited to querying only a single table at a time.  Suppose you
wanted find all the suppliers who live in Boston.

    answer = tselect(STATIC, sups, "city", EQ, "Boston", NULL);

    SELECT * FROM SUPS WHERE CITY = "Boston"


    s#    sname        city        status
    -------    ---------------    ---------------    -------
    1    Smith, John    Boston        5
    3    Barnes, Frank    Boston        15






                     - 52 -

Note:  unless you want to keep the results of a query permanent,
which is rare, you will probably want all of your queries to be of
type STATIC.  Queries nested inside of other queries should always
be of type TEMP.

CRDE can also create compound expressions by adding more conditions
to the search expression.  You may only want those suppliers who live
in Boston and have a status greater than 5.

    answer = tselect(STATIC, sups, "city", EQ, "Boston",
      "status", GT, 5, NULL);

    SELECT * FROM SUPS WHERE CITY = "Boston" AND STATUS > 5


    s#    sname        city        status
    -------    ---------------    ---------------    -------
    3    Barnes, Frank    Boston        15


ORing queries

Notice that CRDE can handle queries in which the conditions are
logically anded together in a fairly straightforward manner.  But
suppose you wanted a list of all suppliers who live in Boston or
have a status > 5? There are several ways to handle this.  The
simplest is to break up the query into 2 separate queries and union
them together with the tunion function.

    answer = tunion(STATIC,
      tselect(TEMP, sups, "city", EQ, "Boston", NULL),
      tselect(TEMP, sups, "status", GT, 5, NULL)
    );

    SELECT * FROM SUPS WHERE CITY = "Boston" OR STATUS > 5


    s#    sname        city        status
    -------    ---------------    ---------------    -------
    1    Smith, John    Boston        5
    2    Jones, Ben    New York    10
    3    Barnes, Frank    Boston        15
    4    Landon, Mark    Auburn        20


tunion is declared in crde.h as

    TABLE *tunion(char *name, TABLE *t1, TABLE *t2);

tunion returns a table containing all the rows found in t1 and t2.
t1 and t2 must be compatible, i.e. they must be identical on a column
by column basis in both type and size.  They may have different
primary and/or secondary keys however.  For example, the following
tables would all be compatible with one another




                     - 53 -

    tcreat(STATIC, "s# i, sname c26, city 26, status i");
    tcreat(STATIC, "*s# i, sname c26, city c26, status i");
    tcreat(STATIC, "sup# i, name c26, loc c26, stat i");

while these are not

    tcreat(STATIC, "s# i, sname c26, city, c26, status i");
    tcreat(STATIC, "s# i, sname c26, city c26");
    tcreat(STATIC,  "s# i, sname c16, city c26, status i");
    tcreat(STATIC,  "s# l, sname c26, city c26, status i");

The structure of the resulting table will be identical to t1, and will
have the same primary key, if any.  This is important to remember
because tunion will may return two different tables depending upon
whether t1 is keyed.

In this particular query, tunion is only effective if sups has a
primary key, which in this case it does.  If sups is not keyed, a
problem can arise if there exist rows in the table where a supplier
lives in Boston and has a status > 10.  If this is true, then both
the innermost tselect statements will collect the same row.  The
difference is that when sups is keyed, the resulting union, which is
also keyed, filters out any duplicate rows automatically.  If sups were
not keyed, then any rows in which the supplier lives in Boston and has
a status > 10 would appear twice in the answer table.

This is just one in many cases where the existence, or absence, of a
primary key can affect the result of a query.  You need to keep this
in mind when designing your tables and the queries you will need to
perform on them.

Another way to perform the query would be to use tselectif and write
a simple "..if" function to perform the query.

    int iffunc(void *rec)  {
      return strcmp(((sup *)rec)->city, "Boston") == 0
        ||  ((sup *)rec)->status > 5;
    }

    answer = tselectif(STATIC, sups, iffunc, NULL);

Either is an acceptable answer to the query.


Projecting columns

CRDE gives you the ability to project columns from a table with its
tproject function.  tproject is much like tselect except it specifies
only those columns which it wants to display.  For example, if you
wanted a list of all supplier names and the city they live in.

    answer = tproject(STATIC, sups, "sname,city", NULL);

    SELECT SNAME, CITY FROM SUPS




                     - 54 -

    sname        city
    ---------------    ----------------
    Smith, John    Boston
    Jones, Ben    New York
    Barnes, Frank    Boston
    Landon, Mark    Auburn
    Andrews, Jim    New York


In CRDE however, it is generally more common to select rows rather than
to project them.  Usually you won't bother to remove those columns
which you don't really need in queries except in special occasions.
However, there is one type of query where tproject shines.  That is
when selecting distinct values from a column.


Selecting distinct values

tproject is primarily used to find distinct values in a column.
tproject expects a special type of column list which allows you to set
the primary key of the resulting table by prepending column names with
an asterisk '*', similar to a table descriptor.  The result of this is
that all duplicate values in the column(s) marked as part of the
primary key will be filtered out automatically by CRDE when the new
table in generated, since no two rows in a table may have the same key
values.

Suppose you wanted a list of all supplier #'s who have made orders.
Several supplier #'s exist more than once in the orders table.
However, by defining the answer table to be "*s#" any duplicates
are filtered out automatically.

    answer = tproject(STATIC, orders, "*s#", NULL);

    SELECT DISTINCT S# FROM ORDERS


    s#
    -------
    1
    2
    3
    4


Joining tables

The heart of any relational language is the ability to relate
information between tables.  In relational terms, this is called
"joining" tables. You can join tables with CRDE's powerful tjoin
function.  The orders table consists of three columns s#, p#, and qty.
Suppose we wanted a list of all orders which included all the details
about the supplier making the order. All the information about
suppliers exists in the sups table.  By using tjoin, you can merge the
information in sups and orders together into a single table.



                     - 55 -

    answer = tjoin(STATIC, sups, "s#", orders, "s#",
      "s#,sname,city,status,p#,qty");

    SELECT S#,SNAME,CITY,STATUS,P#,QTY FROM SUPS,ORDERS WHERE
      SUPS.S# = ORDERS.S#


    s#     sname        city        status    p#    qty
    -------    ---------------    ---------------    -------    -------    -------
    1    Smith, John    Boston        5    1    100
    1    Smith, John    Boston        5    2    200
    1    Smith, John    Boston        5    3    300
    1    Smith, John    Boston        5    4    400
    1    Smith, John    Boston        5    5    500
    1    Smith, John    Boston        5    6    600
    2    Jones, Ben    New York    10    1    100
    2    Jones, Ben    New York    10    2    100
    2    Jones, Ben    New York    10    6    100
    3    Barnes, Frank    Boston        15    1    200
    3    Barnes, Frank    Boston        15    2    50
    4    Landon, Mark    Auburn        20    3    100
    4    Landon, Mark    Auburn        20    5    500


tjoin is declared in crde.h as

    TABLE *tjoin(char *name, char *cl1, TABLE *t1, char *cl2, TABLE
      *t2, char *pl);

tjoin joins t1 and t2 to create a new table.  cl1 and cl2 are column
lists describing the join columns for each table.  Up to 256 columns
may be specified.  tjoin joins those rows from either table which have
identical values in their corresponding  join columns.  Join columns
must be identical in both type and size on a column by column basis:
c columns must be joined to c columns, i columns  must be joined to i
columns, etc.

pl lists those columns to be included in the result table.  pl may
contain from both t1 and t2.  A problem might occur however, if there
exists a column in both t1 adn t2 with the same name.  You could not
include both in the result table because CRDE does not allow tables
with duplicate column names.  If you specify one of the columns, CRDE
will pick the one from t1 by default.  You should take this into
consideration when creating tables you may plan to join later.

Like tproject, tjoin's column list may define a primary key.  You can
use this to remove duplicate values from the result table.  For
example, suppose you wanted a list of supplier's names who have
placed orders.

    answer  = tjoin(STATIC, sups, "s#", orders, "s#", "*s#,sname");

    SELECT DISTINCT S#, SNAME FROM SUPS, ORDERS





                     - 56 -

    s#    sname
    -------    ----------------
    1    Smith, John
    2    Jones, Ben
    3    Barnes, Frank
    4    Landon, Mark


Order by queries

Often you may want to sort a query in a particular order for viewing
or reporting.  Whenever you perform a query in CRDE, using tselect,
tjoin, or any other function which returns a table as a result, the
order of the rows in the answer table is undefined.  If you want to
put them in order you must use the tsort function to sort the answer
table.  Remember the first query where we wanted all the suppliers
who lived in Boston.  Suppose we want that list in alphabetic order
by the supplier's name.  You can do this by simply nesting the query
inside a tsort function call.

    answer = tsort(STATIC,
      tselect(TEMP, sups, "status", GT, 5, NULL),
      "sname"
    );

    SELECT * FROM SUPS WHERE STATUS > 5 ORDER BY SNAME


    s#    sname        city        status
    -------    ---------------    ---------------    -------
    3    Barnes, Frank    Boston        15
    2    Jones, Ben    New York    10
    4    Landon, Mark    Auburn        20


tsort is declared in crde.h as

    TABLE *tsort(char *name, TABLE *t, char *id);

tsort returns a sorted copy of t according to the index descriptor in
id.  You can sort a column in descending order by prepending its name
with an exclaimation point '!'.  For example, to sort the above query
in descending order would look like

    answer = tsort(STATIC,
      tselect(TEMP, sups, "city", EQ, "Boston", NULL),
      "!sname"
    );

    SELECT * FROM SUPS WHERE STATUS > 5 ORDER BY SNAME DESC








                     - 57 -

    s#    sname        city        status
    -------    ---------------    ---------------    -------
    4    Landon, Mark    Auburn        20
    2    Jones, Ben    New York    10
    3    Barnes, Frank    Boston        15


tsort should always be the outermost functions call in a query.


Statistical queries, making calculations

CRDE has special functions to calculate the count, min, max, average,
and sum of a column.  These are tcount, tmin, tmax, taverage, and tsum
respectively.  Unlike in SQL, CRDE stores the result of these queries
in a variable, not a table.

All of CRDE's statistcal functions have the same declaration:

    int statfunc(TABLE *t, char *c, void *result);

where statfunc is tcount, tmin, tmax, taverage, or tsum.  t is the table
being queried.  c is the column to perform the statiscal analysis on.
result is a pointer to a buffer which will hold the result of the query.
The result type is dependent upon the function you are using and the
datatype of the column being queried.  statfunc returns 0 if the query
was successful.

Find the maximum status of any supplier.

    int i;

    tmax(sups, "status", &i);

    SELECT MAX(STATUS) FROM SUPS


    max(status)

    20


CRDE places the result of tmax into i, which must be the same type as
the column being queried, int.  Not all of the statistical query
functions expect a result type to be the same as the column's type
however. For example, tsum and taverage always expect the result to be
of type double, regardless of the column type.

    double d;

    tsum(orders, "qty", &d);

    SELECT SUM(QTY) FROM ORDERS





                     - 58 -

    sum(qty)

    3250


You can take statistical queries of a given subset of a table by simply
nesting a query within the statistical function.  For example, to find
the maximum status of any supplier who lives in Boston

    int i;

    tmax(tselect(TEMP, sups, "city", EQ, "Boston", NULL),
      "status", &i);

    SELECT MAX(STATUS) FROM SUPS WHERE CITY = "Boston"


    max(status)

    15


You might be wondering what tmax would return if the table it is
querying is empty.  You can't find the maximum of nothing.  In a truly
relational language, the result of such a query would be NULL, or
unknown.  However, since CRDE does not support NULL values, it returns
an error code instead, specifying that it could not answer the query.


Set-related queries

CRDE provides two very powerful set-comparison functions: tdiff and
tintersect.  They are declared in crde.h as

    TABLE *tdiff(char *name,  TABLE *t1, TABLE *t2);
    TABLE *tintersect(char *name, TABLE *t1, TABLE *t2);

tdiff creates the relational difference between t1 and t2.  The
resulting table will contain all those rows in t1 which are not found
in t2.  tintersect is just the opposite.  It returns a table containing
all the rows in t1 which are also found in t2.  Like tunion,  t1 and t2
must have compatible structures, i.e. they must be identical in both
type and size on a column by column basis.   The resulting table will
have a structure identical to t1.

This query uses tdiff to determine which suppliers have not made any
orders.

    answer = tdiff(STATIC,
      tproject(TEMP, sups, "s#", NULL),
      tproject(TEMP, orders, "*s#", NULL)
    );

    s#
    -------
    5


                     - 59 -

Notice inside tdiff two sets are created: one containing every supplier #,
and one containing every supplier # who made an order.  The resulting
difference leaves those supplier's who have not made any orders.  Note
that the tables are compatible:  both contain a single column of type i.
Although one table is keyed and one is not, tdiff does not complain.
Two tables do not have to have the same primary key to be compatible.

This query uses tintersect to find those parts ordered by both supplier
#1 and supplier #2.

    answer = tintersect(STATIC,
      tproject(TEMP, orders, "p#", "s#", EQ, 1, NULL),
      tproject(TEMP, orders, "p#", "s#", EQ, 2, NULL)
    );


Complex Queries

Subqueries

Suppose you wanted a list of all suppliers who have a status greater
than the average status.

    double d;

    taverage(sups, "status", &d);
    answer = tselect(STATIC, sups, "status", GT, (int)d, NULL);

    SELECT * FROM SUPS WHERE STATUS > (SELECT AVG(STATUS) FROM S)


    s#    sname        city        status
    -------    ---------------    ---------------    -------
    3    Barnes, Frank    Boston        15
    4    Landon, Mark    Auburn        20


Notice how d must be typecast (int) in the tselect statement because
the result of taverage is always double.


Correlated subqueries

A correlated subquery is a special type of subquery.  In a correleated
subquery, the calculated subquery is dependent upon the current row
being queried.  In SQL, this type of query requires that you use an
alias.  In CRDE it requires and "..if" function.

This query returns all suppliers which have a status greater than
the average status for that supplier's particular city.








                     - 60 -

    int iffunc(void *rec)  {
        double d;
        int i;

        i = taverage(
          tselect(TEMP, sup, "city", EQ, ((sup *)rec)->city, NULL),
          "status", &d
        );
        return i ? i : ((sup *)rec)->status >= d;
    }

    answer = tselectif(STATIC, sups, iffunc, NULL);

    SELECT * FROM SUPS X WHERE STATUS >= (SELECT AVG(STATUS)
      FROM S WHERE CITY = X.CITY)


    s#    sname        city        status
    -------    ---------------    ---------------    -------
    2    Jones, Ben    New York    10
    3    Barnes, Frank    Boston        15
    4    Landon, Mark    Auburn        20


CRDE uses the tselectif function to perform this query.  Every row in
the table is passed to iffunc for evaluation.  iffunc calculates the
average status of all suppliers for that row's city, then returns true
if the status for the row is greater than that average.


Multi-table joins

In relational databases multi-table (3 or more) joins are commonplace.
Although CRDE does not directly support multi-table joins, you can
simulate them using multiple tjoin calls.  The technique for a three
table join is to join the first two tables together, and then join the
third table to the result.  To perform a four table join, simply
perform a three table join, and then join the fourth table to the
result, and so on.  There is no limit to the number tables which can
be joined in this fashion.

The following demonstrates the three table join by joining the sups,
parts, and orders table into a single table.  The query is split into
two parts (instead of being nested) for clarity.

    temp = tjoin(TEMP, parts, "p#", orders, "p#", "s#,p#,pname,qty");

    answer = tjoin(STATIC, sups, "s#", temp, "s#",
      "s#,sname,p#,pname,qty"
    );

    SELECT * FROM SUPS,PARTS,ORDERS WHERE SUPS.S# = ORDERS.S#
      AND PARTS.P# = ORDERS.P#





                     - 61 -

    s#    sname        p#    pname        qty
    -------    ---------------    -------    ---------------    -------
    1    Smith, John    1    bolt        100
    1    Smith, John    2    screw        200
    1    Smith, John    3    screw        300
    1    Smith, John    4    nut        400
    1    Smith, John    5    bolt        500
    1    Smith, John    6    nail        600
    2    Jones, Ben    1    bolt        100
    2    Jones, Ben    2    screw        100
    2    Jones, Ben    6    nail        100
    3    Barnes, Frank    1    bolt        200
    3    Barnes, Frank    2    screw        50
    4    Landon, Mark    3    screw        100
    4    Landon, Mark    5    bolt        500


As in the two table join, it makes no difference in which order the
tables are joined.  The result will always be the same.


"In" subqueries

Suppose you want a list of all suppliers who are supplying red parts.
This query requires two steps, first creating a set containing all red
parts, then selecting every supplier who supplies a part in the set.
You must remember to drop the set when the query is finished.  You
cannot make set TEMP because it would be automatically dropped the
first time iffunc is called.

    int iffunc(void *rec)  {
      return tlookup(set, "p#", EQ, ((order *)rec)->p, NULL);
    }

    set = tproject(STATIC, parts, "p#", "color", EQ, "red", NULL);
    answer = tprojectif(STATIC, orders, "*s#", iffunc, NULL);
    tdrop(set);

    SELECT DISTINCT S# FROM ORDERS WHERE P# IN
      (SELECT P# FROM PARTS WHERE COLOR = "red")


    s#
    -------
    1
    2
    3


Multiple subqueries

To get the name of every supplier who is supplying red parts.






                     - 62 -

    TABLE *set1, *set2;

    int iffunc1(void *rec)  {
      return tlookup(set, "p#", EQ, ((order *)rec)->p, NULL);
    }

    int iffunc2(void *rec)  {
      return tlookup(set, "s#", EQ, ((sup *)rec)->s, NULL);
    }

    set1 = tproject(STATIC, parts, "*p#", "p#", EQ, "red", NULL);
    set2 = tprojectif(STATIC, orders, "*s#", iffunc1, NULL);
    tdrop(set1);
    answer = tselectif(STATIC, sups, iffunc2, NULL);
    tdrop(set2);

    SELECT * FROM SUPS WHERE S# IN
      (SELECT S# FROM ORDERS WHERE P# IN
        (SELECT P# FROM PARTS WHERE COLOR = "red"))


    s#    sname         city        status
    -------    ---------------    ---------------    -------
    1    Smith, John    Boston        5
    2    Jones, Ben    New York    10
    3    Barnes, Frank    Boston        15


This query is simply a subquery within a subquery, more specifically,
and "in" subquery within an "in" subquery.  Thus it requires two "..if"
function calls instead of just one.

Another way to do this would be to perform this query would be to take
the answer from the original "in" query, and join it to the sups table.
Either is a legitimate answer to the query.


Group By queries

CRDE performs group by queries with the tgroup function.  tgroup
requires you to write a special group function which works with tgroup
to create the answer table.  First, an example is shown to illustrate
what a common tgroup query will look like.  Suppose you want a list of
the number of suppliers, per city.














                     - 63 -

    int groupfunc(void *in, void *out, int first)  {
      typedef struct {
        char city[26];
        int count;
      } ans;

      switch (first) {
        case 1 :
          strcpy(((ans *)out)->city, ((sup *)in)->city);
          ((ans *)out)->count = 0;

        case 0 :
          ((ans *)out)->count++;
          break;
      }
      return 1;
    }

    answer = tgroup(STATIC, sups, "city", "city c26, count i",
      groupfunc);

    SELECT CITY,COUNT(S#) FROM S GROUP BY CITY


    city        count
    ---------------    -------
    Auburn        1
    Boston        2
    New York    2


tgroup is declared in crde.h as

    TABLE *tgroup(char *name, TABLE *t, char *cl, char *td,
      int (*summary)(void *in, void *out, int first));

The cl parameter in tgroup lists the column(s) to group on. You can
group on up to 8 columns with tgroup.  All rows in the table with the
same values in the columns in cl belong to the same group.  In the
above example, rows are grouped by city.  Thus all rows with the same
city belong to the same group.

td is a table descriptor describing the resulting table.  td  may be
anything you wish.  summary is responsible for creating the table
described by td.

summary is a user-defined group function which actually generates the
result table.  Group functions must follow a specific set of rules to
work correctly.


Writing grouping functions

summary should have a declaration similar to

    int summary(void *in, void *out, int first);


                     - 64 -

Every row in each group is passed to summary and is referenced by in.
The information in used to calculate the output row for the group.  out
is a pointer to the output row for a particular group.  summary is
responsible for building out.  first is a parameter used to determine
whether a row is the first or last element in the group.  The
information is used to determine when to initialize the group and
whether to include the group in the answer table.  The following
describes how tgroup uses summary to generate the answer table.

    1. tgroup calls summary with first == 1.  in points to the first
    row in the group.  summary should initialize out at this point.

    2. tgroup calls summary with first == 0 for every other row in
    the group.  Each row is accessed through the pointer in.
    summary should update any calculated columns in out based
    on each row in the group.

    3. After every row in the group has been passed to summary,
    tgroup calls summary once more time with first == -1.
    summary should perform any final calculations (now that all
    rows in the group have been processed), and do any cleanup
    necessary.  out should now contain the values which will
    appear in the answer table.  However, summary can also decide
    whether or not it wants to include out in the answer table.
    If summary returns > 0 then tgroup will include out.  If
    summary returns 0 then tgroup will not include the row.

tgroup repeats step 1-3 for every group in the table.  In steps 1. and
2., summary  should return a value >= 0 if no errors occurred.
Regardless of the value of first, if the summary  returns a value < 0,
tgroup aborts and returns NULL.  terrno will be set to the value
returned by summary.  A well designed summary should include error-
handling and perform any necessary cleanup if an error occurs.

To now describe the operation of  the first example, tgroup groups the
table by "city".  The resulting table will contain the city of the
group and the number of suppliers in that city ("city c26, count i").
tgroup then starts calling groupfunc for each row in each group to
generate the answer table.

groupfunc accepts the first row  of the first group which happens to be
"Auburn".  first equals 1 so control goes to the first case in the switch
statement

    case 1 :
      strcpy(((ans *)out)->city, ((sup *)in)->city);
      ((ans *)out)->count = 0;

out  is initialized to "city "= "Auburn" and "count" = 0.  Notice that
out is typecast with ans. ans must be a structure typedef which is
identical to the answer table being generated.  in, of course, must be
typecast by sup.

Since in  is also the first row in the group, we have to update "count"
in out.  So control passes on to the next case in the switch statement
(since there is no break statement)


                     - 65 -

    case 0 :
      ((ans *)out)->count++;
      break;

case 0, which is used by every other row in the group besides the first,
increments the "count" in out.  So "count" now equals 1.  Control now
passes to the

    return 1;

statement, and groupfunc returns successfully to tgroup.

tgroup normally would processes every other row in the group (all those
suppliers who live in Auburn) to groupfunc, with first set to 0.
groupfunc would then increment "count" in out for each row.  However,
there happens to be only one row in the sups table who lives in Auburn,
so tgroup immediately calls groupfunc with first set to -1.  groupfunc
simply returns 1 in this case, indicating that tgroup should insert out
should in the answer table.  At this point, the answer table looks like


    city        count
    ---------------    -------
    Auburn        1


tgroup now moves on to the next group, which happens to be "Boston".
The first row in the group (either John Smith or Frank Barnes,
whichever happens to be picked first by tgroup) is sent to groupfunc;
first of course, equals 1.  out is initialized to "city" = "Boston",
"count" = 1.  tgroup calls groupfunc again with the other row in the
group, with first set to 0.  "count" is incremented to 2.  groupfunc
is called once more with first set to -1, which immediatly returns and
the new out  is inserted.  The answer table now looks like


    city        count
    ---------------    -------
    Auburn        1
    Boston        2


tgroup repeats the process for the final group, "New York", which also
has two suppliers.  After the final group is completed, tgroup returns
the generated table as its result.


    city        count
    ---------------    -------
    Auburn        1
    Boston        2
    New York    2






                     - 66 -

Example #2.

Calculate the sum of all parts ordered by each supplier.

    int calcsum(void *in, void *out, int first)
    {
      typedef struct {
        int s;
        int sum;
      } group;

      switch (first) {
        case 1 :
          ((group *)out)->s   = ((order *)in)->s;
          ((group *)out)->sum = 0;

        case 0 :
          ((order *)out)->sum += ((order *)in)->quant;
          break;
      }
      return 1;
    }

    answer = tgroup(STATIC, orders, "s#", "s# i, sumofqty i", calcsum);

    SELECT S#,SUM(QTY) FROM ORDERS GROUP BY S#

    s#    sumofqty
    -------    -----------
    1    2100
    2    300
    3    250
    4    600


Example #2 is similar to the first example except that a column is
summed instead of counted.


Example #3.

This example demonstrate how the -1 flag in first can be used to find
a group average.

    int calcavg(void *in, void *out, int first)
    {
      typedef struct {
        int s;
        double avg;
      } group;
      static int i;

      switch (first) {
        case 1 :
          ((group *)out)->s   = ((order *)in)->s;
          ((order *)out)->avg = 0.0;
          i = 0;

                     - 67 -

        case 0 :
          ((group *)out)->avg += ((order *)in)->quant;
          i++;
          break;

        case -1 :
          ((order *)out)->avg /= i;
          break;
      }
      return 1;
    }

    answer = tgroup(STATIC, orders, "s#", "s# i, sumofqty i", calcavg);

    SELECT S#,AVG(QTY) FROM ORDERS GROUP BY S


calcavg is identical to calcsum except that it contains the extra case

    case -1:
      ((order *)out)->avg /= i;
      break;

This calculates the average of the group by dividing the sum of all
the orders by the number of orders made.  Note that this can only be
done by taking advantage of the fact that tgroup calls calcavg with
first == -1 at the end of each group.

As you can see, group functions are very similar to the if functions
described in Chapter 7, and provide an almost endless range of
possibilities.  Like if functions, group functions place the host
table in a special read-only mode, so that you may not modify it.
However, no other restrictions are imposed by group functions.


Group Having queries

You can simulate the HAVING clause of an SQL statement by simply
placing the results of a tgroup within a tselect function.  For
example, suppose you want only those rows generating in example #2
having a sum > 500.

    answer = tselect(STATIC,
      tgroup(TEMP, orders, "s#", "s# i, sumofqty i", calcsum),
      "sumofqty", GT, 500, NULL
    );

    SELECT S#,SUM(QTY) GROUP BY S# HAVING SUM(QTY) > 500


    s#    sumofqty
    -------    -----------
    1    2100
    4    600




                     - 68 -

You can also specify a HAVING clause in your summary function by having
it return 1 or 0 when first == -1 depending upon whether you want to
include the row or not.  For example, you could duplicate the above
query without the tselect statement by adding the following case to
calcsum

    case -1 :
      return ((group *)out)->sum > 500;


Set Group queries

Grouping functions, like "if" functions, provide querying possibilities
which go far beyond even the capabilities of even high level query
languages like SQL.  One such type of query is one which groups rows
based upon sets of data.  A set of data might be something like all red
parts.  With a set related query you could then ask questions like

    Which suppliers supply no red parts?
    Which suppliers supply only red parts?
    Which suppliers supply every red part?
    Which suppliers supply exactly red parts?


Which suppliers supply no red parts?

    int groupno(void *in, void *out, int first)
    {
      static int flag;

      switch (first) {
        case  1 :
          *(int *)out = ((order *)in)->s; flag = 0;
        case  0 :
          if (tlookup(set, "p#", EQ, ((order *)in)->p, NULL))
        flag = 1;
          break;
        case -1 :
          return !flag;
      }
    }

    set = tproject(STATIC, parts, "p#", "color", EQ, "red", NULL);
    answer = tgroup(STATIC, orders, "s#", "s# i", groupno);
      tdrop(set);


    s#
    -------
    4


groupno requires that a set be created before the queried is executed.
You must remember to remove the set afterwards.




                     - 69 -

You may have noticed that the answer to the groupno query does not
contain supplier #5!  Is there an error in groupno?  Well, yes and
no.  groupno  does select only those suppliers which supply no red
parts, but only those which supply at least one part.  Any suppliers
who do not supply any parts will not appear in the groupno query.
This is because the group set occurs on the orders table, not the sups
table.  Fortunately, grouponly, groupevery, and groupexactly do not
suffer from this problem since they only work with suppliers who do
supply parts.


What suppliers supply only red parts?

    int grouponly(void *in, void *out, int first)
    {
      static TABLE *gset;
      int i;

      switch (first) {
        case  1 :
          gset = tcreat(STATIC, "*p# i");
          *(int *)out = ((order *)in)->s;
        case  0 :
          tload(gset, ((order *)in)->p);
          break;
        case -1 :
          i = trows(tdiff(TEMP, gset, set)) == 0;
          tdrop(gset);
          return i;
      }
      return 1;
    }

    set = tproject(STATIC, parts, "p#", "color", EQ, "red", NULL);
    answer = tgroup(STATIC, orders, "s#", "s# i", grouponly);
    tdrop(set);


    s#
    -------
    2
    3


Like all the group set queries, grouponly requires that a set containing
all red parts be created before the actual query is performed.
grouponly may be the most complex query we have seen so far.  It
requires that the group function itself generate a table, then perform
a set comparison.  grouponly, groupevery and groupexactly are very
similar in design.








                     - 70 -

This is how it works.  grouponly performs the query by creating a set
of all the parts ordered by suppliers in that particular group, gset.
grouponly then compares gset to set with the tdiff  function.  If the
difference of gset - set yields a table with 0 rows, that means that
gset contained only those parts found in set.  Hence, every part
supplied by that supplier (gset) was a red part (set).

When grouponly is called with first equals 1, it creates the gset table.
It only needs to contain one column, p#, and it fact it must, since it
needs to be compatible with set so that they will work with tdiff.
out is also initalized at this point to the current group's supplier #.
Since gset is declared within grouponly it must be declared as static,
otherwise the table would disappear every time grouponly returned to
tgroup.

grouponly then loads gset with the part#'s ordered by that supplier.
When every row in the group has been processed, gset will contain
every part# ordered by that supplier.  Notice that the p# column in
gset is declared as the primary key (as is set). This is not required.
grouponly will work fine is either or both are not keyed.  However, on
larger tables, creating the sets with a primary key will most likely
improve performance.

Now the tricky part.  grouponly now must compare the two sets.  It does
this after the entire group has been processed, when first equals -1.
tdiff is used to compare gset to set.  As explained above, if the
difference between the two yields a table with 0 rows, then grouponly
returns 1, meaning tgroup should include out in the answer table,
otherwise, grouponly returnes 0.  Importantly, grouponly also drops
the gset table it created.   If it didn't the table would lie around
in memory until the program terminated.


What suppliers supply every red part?

    int groupevery(void *in, void *out, int first)
    {
      static TABLE *gset;
      int i;

      switch (first) {
        case  1 :
          gset = tcreat(STATIC, "*p# i");
          *(int *)out = ((order *)in)->s;
        case  0 :
          tload(gset, ((order *)in)->p);
          break;
        case -1 :
          i = trows(tdiff(TEMP, set, gset)) == 0;
          tdrop(gset);
          return i;
      }
      return 1;
    }




                     - 71 -

    set = tproject(STATIC, parts, "p#", "color", EQ, "red", NULL);
    answer = tgroup(STATIC, orders, "s#", "s# i", groupevery);
    tdrop(set);


    s#
    -------
    1
    2

groupevery is identical to grouponly except that in the tdiff statement,
gset and set are switched around.  Since set contains every red part,
if the difference of set and gset is 0, then gset  must also contain
every red part.  gset can also contain parts that are not red, just as
long as it contains every one that is.


Which suppliers supply exactly red parts?

    int groupexactly(void *in, void *out, int first)
    {
      static TABLE *gset;
      int i;

      switch (first) {
        case  1 :
          gset = tcreat(STATIC, "*p# i");
          *(int *)out = ((order *)in)->s;
        case  0 :
          tload(gset, ((order *)in)->p);
          break;
        case -1 :
          i = trows(tdiff(TEMP, gset, set)) == 0 &&
          trows(tdiff(TEMP, set, gset)) == 0;
          tdrop(gset);
          return i;
      }
      return 1;
    }

    set = tproject(STATIC, parts, "p#", "color", EQ, "red", NULL);
    answer = tgroup(STATIC, orders, "s#", "s# i", groupexactly);
    tdrop(set);


    s#
    -------
    2










                     - 72 -

groupexactly is a cross between grouponly and groupevery.  Common
sense tells use this is true.  For gset to exactly match set it must
contain every part in set, and only those parts in set.  Thus
groupexactly tests for both.  If either is false then the sets are
not exactly alike.  Only one supplier, #2, supplies exactly red parts.
Notice the supplier #2 is the only supplier found in both the grouponly
result and the groupevery result.


Optimizing Queries

As queries become more and more complex, the variety of ways in which
they can be accomplished increases.  But which way is best?  By
following some general guidelines, you can help improve the performance
times of your queries.  The following is a list of functions in the
order or level at which they should pear in a query.  When performing
queries which combine two or more of these functions, the ones at the
bottom of the list should be performed before, or within, those at the
top of the list.

    tsort
    tunion
    tjoin
    tintersect
    tdiff
       tprojectif
    tselectif
    tproject
    tselect

If you want the results of a query sorted, tsort should always be the
last or outermost function.  It doesn't make any sense to nest tsort
within another query.  Also, use temporary tables (TEMP and STATIC)
whenever possible.  CRDE can often hold the intermediate results of a
query completely in memory, thus improving performance.























                     - 73 -






Chapter 12

Creating Reports



Single-table reports

In the last section we discussed how to use CRDE to generate queries
and ask questions about the data in your database.  Often you will
want to keep the results of a query permanent, on paper.  The CRDE
function usually used for generating reports is tscanif.  tscanif
doesn't do anything accept call its if function for every row in the
table.  If you tell the if function to print out those rows, you can
create a hard copy report of your tables and queries.

This simple example prints out a report on the sups table.

    int line, page;
    char *title;

    #define LINESPERPAGE 54

    int ReportSups1(void *rec)
    {
      /* print report header at top of page */
      if (line == 0) {
      fprintf(stdprn, "%*s\n", 40 + strlen(title), title);
      fprintf(stdprn, "%41s %d\n\n", "PAGE", page);
      fprintf(stdprn, "  s#            sname           \n");
      fprintf(stdprn, "------ "  "-------------------------\n");
        line += 5;
      }

      /* print a row */
      fprintf(stdprn, "%6d %-25s\n", ((sup*)rec)->s,
        ((sup *)rec)->sname);

      /* test for end of page */
      if (line % LINESPERPAGE == 0) {
        fprintf(stdprn, "\12");
        line = 0;
      }

      return 1;
    }

    line = 0; page = 1;
    title = "SUPPLIER REPORT";
    tscanif(sups, ReportSups1, NULL);



                     - 74 -

The above ReportSups1 report is generic enough to be used in a variety
in situations, namely any query which generates a subset of the sups
table.  For example, this portion of code uses the ReportSups1 to
generate a report listing all those suppliers with an above average
status.

    double d;

    taverage(sups, "status", &d);
    answer = tsort(STATIC,
      tselect(TEMP, sups, "status", GT, (int)d, NULL),
      "sname"
    );

    line = 0; page = 1;
    title = "SUPPLIERS WITH ABOVE AVERAGE STATUS";
    tscanif(answer, ReportSups1, NULL);

The above report uses the same report function, ReportSups1, to output
a list of all suppliers with above average status, sorted by supplier
name.  Of course, since tscanif is a search-related function, simple
queries can be performed by tscanif itself.  For example to list only
those suppliers in Boston

    line = 0; page = 1;
    title = "SUPPLIERS IN BOSTON";
    tscanif(sups, ReportSups1, "city, EQ, "Boston", NULL);

However, since most reports either are more complex, or require that
the report be sorted, you will generally find it more common to create
a separate query rather than filter a table through tscanif.


Multi-table reports

You can produce multi-table reports with CRDE just as easily as single-
table reports.  There are two basic methods for mutli-table reports:
the join method, and the lookup method.


The join method

The join method requires that you create the multi-table report by
joining all the linked tables together into a single table, and then
produce the report.  For example, suppose you wanted to create a
verbose orders report containing all orders with both supplier and
part information.











                     - 75 -

    int line, page;
    char *title;

    #define LINESPERPAGE 54

    int ReportOrders1(void *rec)
    {
      /* this typedef must exactly match joined table's structure */
      typedef struct {
        int s;
        char sname[26];
        char city[26];
        int status;
        int p;
        char pname[16];
        char color[11];
        int qty;
      } spo;

      /* print report header at top of page */
      if (line == 0) {
        fprintf(stdprn, "%*s\n", 40 + strlen(title), title);
        fprintf(stdprn, "%41s %d\n\n", "PAGE", page);
        fprintf(stdprn, "s#     sname                   "
          " city                     status p#     pname "
          " color qty\n");
        fprintf(stdprn, "------ ------------------------- "
          "--------------- ------ --------------- ---------- "
          "------\n");
        line += 5;
      }

      /* print an order */
      fprintf(stdprn, "%6d %-25s %-25s %6d %6d %-15s %-10s %6d\n",
        ((spo *)rec)->s,
        ((spo *)rec)->sname,
        ((spo *)rec)->city,
        ((spo *)rec)->status,
        ((spo *)rec)->p,
        ((spo *)rec)->pname,
        ((spo *)rec)->color,
        ((spo *)rec)->qty
      );

      /* if end of page, form feed */
      if (line % LINESPERPAGE == 0) {
        fprintf(stdprn, "\12");
        line = 0;
      }

      return 1;
    }






                     - 76 -

    answer = tjoin(TEMP, sups, "s#",
      tjoin(TEMP, part, "p#", orders, "p#","s#,p#,pname,color,qty"),
      "s#",
      "s#,sname,city,status,p#,pname,color, qty"
    );

    line = 0; page = 1;
    title = "ORDERS REPORT";
    tscanif(answer, ReportOrders1, NULL);


The lookup method

The lookup method takes a different approach.  It scans each row in
the orders table, then uses the tget  function to retrieve supplier
and part information.

    int line, page;
    char *title;

    int ReportOrders2(void *rec)
    {
      sup srec;
      part prec;

      /* print report header at top of page */
      if (line == 0) {
        fprintf(stdprn, "%*s\n", 40 + strlen(title), title);
        fprintf(stdprn, "%41s %d\n\n", "PAGE", page);
        fprintf(stdprn, "s#     sname                   "
          " city                     status p#     pname "
          " color qty\n");
        fprintf(stdprn, "------ ------------------------- "
          "--------------- ------ --------------- ---------- "
          "------\n");
        line += 5;
      }

      /* look up supplier */
      i = tget(sups, &srec, 1, "s#", EQ, ((order *)rec)->s, NULL);
      if (i < 0) return i;

      /* look up part */
      i = tget(parts,  &prec, 1, "s#", EQ, ((order *)rec)->p, NULL);
      if (i < 0) return i;

      /* print an order */
      fprintf(stdprn, "%6d %-25s %-25s %6d %6d %-15s %-10s %6d\n",
        ((sup *)srec)->s,
        ((sup *)srec)->sname,
        ((sup *)srec)->city,
        ((sup *)srec)->status,
        ((part *)prec)->p,
        ((part *)prec)->pname,
        ((part *)prec)->color,
        ((order *)rec)->qty
      );

                     - 77 -

      /* if end of page, form feed */
      if (line % LINESPERPAGE == 0) {
        fprintf(stdprn, "\12");
        line = 0;
      }

      return 1;
    }

    line = 0; page = 1;
    title = "ORDERS REPORT";
    tscanif(answer, ReportOrders1, NULL);

The method presented with tscanif is not the only possibility for
creating reports, although it is probably the most common.  tget could
be used to place rows in an array where they could be manipulated
freely.  tgroup is also a possibility for reporting, using its
automatic grouping abilities to output reports based upon groups
and subtotals.  In reality, the possibilities are endless.  CRDE's
intimate relationship with the C language gives it the flexibility to
create even the most complex reports.





































                     - 78 -






Chapter  13

Maintaining Database Integrity



The term "database integrity" refers to the accuracy or correctness
of data in the database.  Making sure the data in a database is correct
is a very important job.  Many data managers, including CRDE, provide
assistance to support database integrity.  Integrity in relational
databases can be broken down into three basic categories:  entity
integrity, domain integrity, and referential integrity.


Entity integrity

Entity integrity refers to the fact that the key values in a
relational table should be unique in that table.  This sort of
integrity is handled automatically by CRDE whenever you define a
primary key on a table.  No matter what operations you perform upon
a keyed table, CRDE always guarantees the uniqueness of its key values
within the table.  CRDE allows you to get around the entity integrity
rule by creating non-keyed tables.  Non-keyed tables can be useful in
a number of situations.


Domain integrity

Domain integrity refers to the correctness of data in a particular
column.  Every column has a practical set of values, a domain, which
it should hold.  For example, the domain for the "age" column is
probably an integer between 1 and 99, the domain for the "sex" column
is either "Male" or "Female", etc.  A data management system which
supports domain integrity would not allow you to enter 800 into the
"age" column, for example.

CRDE does not support domain integrity directly, i.e. there are no CRDE
functions which you can call to set the domain of a particular column.
However domain integrity is relatively simple to implement using C.
For example, you might write a function which would screen the input
of suppliers so that the "status" column contained a value between 0
and 99.

    int InsertSup(sup *s)
    {
      if (s->status < 1 || s->status > 99)
        return 0;
      return tinsert(sups, s, 1);
    }




                     - 79 -

InsertSup returns 1 if the row was inserted, or 0 if it was not.  You
might want to improve InsertSup by insuring that all text columns in
the row are set to upper case.

    int InsertSup(sup *s)
    {
      if (s->status < 1 || s->status > 99)
        return 0;
      strupr(s->sname);
      strupr(s->city);
      return tinsert(sups, s, 1);
    }


Referential integrity

Referential integrity refers to the existence of references between
tables which are linked by key column(s).  For example, the orders
table has two columns, s# and p#, which are used as references to the
sups and parts tables respectively.  If there existed an order in the
orders table like

    s#    p#    qty
    -------    -------    -------
    1    1    100


there must also exists a supplier #1 in the sups table and a part #1
in the parts table otherwise the order makes no sense.

Although CRDE does not support referential integrity directly, its
high level functions make the task almost trivial.


Foreign keys and primary keys

The concept of a foreign key  is the basis for referential integrity.
As we know, a primary key consists of columns in a table which may not
contain duplicate values.  A foreign key is/are column(s) in another
table which reference the table's primary key.  For example, the "s#"
column in the orders table is a foreign key to the sups table because
it references the "s#" column in sups.  Likewise, the "p#" column is a
foreign key to the parts table.  For refential integrity to remain
intact, there must always exists a primary key for each foreign key in
a database.


Referential integrity and inserting

When you insert rows into a table, you want to be sure that any foreign
key columns have references.  This is easily handled with CRDE's tlookup
function.  The following function inserts attempts to insert a row into
the orders table with full referential checking.  Lookups are made to
insure that the order's supplier # and part # are valid.




                     - 80 -

    int InsertOrder(order *o)
    {
      int  i;

      i = tlookup(sups, "s#", EQ, o->s, NULL);
      if (i > 0) {
        i = tlookup(parts, "p#", EQ, o->p, NULL);
        if (i > 0)
          return tinsert(orders, &o, 1);
      }
      return i;
    }

InsertOrder uses the tlookup function to insure that referential
integrity will remain intact when the order is inserted.  InsertOrder
contains full error-checking, and is a good model of how to use CRDE
functions in real applications.  InsertOrder  returns 1 if successful,
or 0 if referential integrity would be comprimised.  If an error
occurs (like a disk seek error), InsertOrder returns the correct error
code of the offending error.


Referential integrity and deleting

There is a similar problem when deleting rows from tables.  Care must
be taken so that a reference for some table is not accidentally deleted.
If you want to delete a supplier from the sups table, you must be sure
that referential integrity is maintained with the orders table.  The
following demonstrates four different methods to implement referential
integrity when deleting a supplier.

    int DeleteSup1(int supno)
    {
      int i;

      i = tlookup(orders, "s#", EQ, supno, NULL);
      if (i == 0) {
        return tdelete(sups, "s#", EQ, supno, NULL);
      }
      return i < 0 ? i : 0;
    }

DeleteSup1 is similar to the SQL command ON DELETE RESTRICT.  It
restricts you from deleting a supplier if any orders reference it.
DeleteSup1 returns > 0 if successful, 0 if deletion was restricted,
or < 0 if an error occurred.

    int DeleteSup2(int supno)
    {
      int i;

      i = tdelete(orders, "s#", EQ, supno, NULL);
      if (i < 0)
        return i;
      return tdelete(sups, "s#", EQ, supno, NULL);
    }


                     - 81 -

DeleteSup2 is similar to the SQL notation ON DELETE CASCADE.  In this
case, if a supplier is deleted, all its orders are deleted as well.
DeleteSup2 follows the same return value scheme as DeleteSup1.

    int DeleteSup3(int supno)
    {
      int i;

      i = tdelete(orders, "s#", -1, NULL, "s#", EQ, supno, NULL);
      if (i < 0)
        return i;
      return tdelete(sups, "s#", EQ, supno, NULL);
    }

DeleteSup3  is similar to the SQL notation ON DELETE SET NULL.  If a
supplier is deleted, the "s#" reference in all its orders are set to
a special value, -1, marking them as unreferenced orders.  The program
might use this information to print a report containing all those orders
made by suppliers since deleted.  DeleteSup3  follows the same return
value scheme as DeleteSup1.

    TABLE *deleted;

    deleted = tborrow("deleted", orders);

    int DeleteSup4(int supno)
    {
      int i;

      i = tadd(deleted, tselect(TEMP, orders, "s#", EQ, supno, NULL));
      if (i < 0)
        return i;
      i = tdelete(orders, "s#", EQ, supno, NULL);
      if (i < 0)
        return i;
      return tdelete(sups, "s#", EQ, supno, NULL);
    }

DeletSup4  is similar to DeleteSup3 except that any deleted orders
are first saved in a special deleted table, so that they may be
referenced later.  deleted is created with the identical structure
or orders, using the tborrow function.  DeleteSup4 follows the same
return value scheme as DeleteSup1.

Notice that because referential integrity is not built in, CRDE gives
you the ability to implement referential integrity in a variety of ways,
not just limited to the ones shown above.


Referential integrity and changing.

Problems with referential integrity can occur when referenced values
are changed in a table.  This can happen, for example, if a supplier's #
is changed and the orders table is not kept in sync.




                     - 82 -

    int ChangeSup1(int oldno, int newno)
    {
      int i;

      i = tchange(sups, "s#", newno, NULL, "s#", EQ, oldno, NULL);
      if (i > 0) {
        i = tchange(orders, "s#", newno, "s#", EQ, oldno, NULL);
        if (i < 0)
          return i;
        return 1;
      }
      return  i < 0 ? i : 0;
    }

Changing the supplier # however introduces a new variable: you may
accidently try to change the "s#" to a value which already exists.
What will tchange do in this case?  If changing a row would violate
the primary key of a table, tchange will not change the row and returns
0, for 0 rows changed.  If tchange can change the row, then it will
return 1, indicating that the "s#" was changed.

ChangeSup1  returns 1 if the supplier # was changed, 0 if it could
not because the new supplier # already existed, or an error code (< 0)
if an error occurred.


































                     - 83 -






Chapter 14

Performing Transactions


CRDE's transaction tracking features

CRDE provides complete transaction tracking facilities.  A transaction
is a series of changes made to a table.  CRDE allows you to track
transactions on a table by table basis.

    int ttransact(TABLE *t);
    int trollback(TABLE *t);
    int tcommit(TABLE *t);
    int tmark(TABLE *t);

The ttransact function begins a transaction on a table.  Once ttransact
is called, all changes made to the table are pending until you decide
to keep or throw out the changes.  A call to trollback returns that
table back to its state prior to beginning the transaction.  tcommit
makes any changes during the transaction permanent.  In either case,
the transaction is considered over.  To start another transaction, you
must call the ttransact function again.  You can use the tmark to
determine whether a table is in a transaction or not.  tmark returns 1
if a table is in a transaction, and 0 if it is not.  If you close a table
which is in the middle of a transaction, the transaction is automatically
committed before the table is closed.  Similarly, if you drop a table
during a transaction, the transaction is automatically rolled back before
the table is dropped.

CRDE places few restrictions on what you can do with tables during a
transaction.  You can query and update them like any other table.  The
only limitation made by CRDE is that you may not create or drop secondary
indexes.  Attempting to do so will result in an error.

The following example demonstrates CRDE's transaction tracking
capabilities at work.

    include <conio,h>
    include <stdio.h>
    include "crde.h"

    int tbuffers = 256;     /* set as large as possible when doing
                transaction tracking */

    int ShowTable(TABLE *t)
    {
      clrscr();
      gotoxy(1, 1); cputs("Displaying a table.");
      gotoxy(1, 2); cprintf("%ld rows found.", trows(t));
      return tview(t, NULL, NULL, 3, 3, 78, 24, 0x07, 0x07, 0x07, NULL);
    }

                     - 84 -

    int main()
    {
      TABLE *t;

      /* open and display table */
      t = topen("sups");
      ShowTable(t);

      /* begin transaction */
      ttransact(t);

      /* perform operation(s) on table */
      tdelete(t, "city", EQ, "Boston", NULL);
      ShowTable(t);

      /* restore original table with single function call! */
      trollback(t);
      ShowTable(t);

      tclose(t);
    }


How does transaction tracking work?

To be able to rollback a table in a transaction, CRDE must record all
the changes you make to the table while the transaction is in effect.
CRDE keeps track of these changes in memory. This has several advantages,
the greatest of which is speed. This allows you to use transaction
tracking  with virtually no performance degradation.  The significant
drawback of this is that you are limited to the number of changes you
can make by the amount of available memory in your computer.  If the
tables you are performing transactions on are large and/or numerous,
you may run out of memory.  When this happens, CRDE functions will start
returning -11 "out of memory" error.  If memory runs out you should
immediately rollback any transactions before continuing.  The amount of
transactions which can be performed depends upon many factors:  the
number and size of table in the transaction, the number of changes
made, and available memory.  If the tables are small enough you will
be perform an unlimited amount of transactions.  To perform an
unlimited amount of transactions on a table, there should be at least

    size of table (on disk)  + size of indexes (on disk)

bytes of memory set aside in your buffers (see Chapter 2 about setting
the tbuffers system variable).  This is a rough estimate which does not
take into account other factors which could possibly make the requirements
steeper.  For optimum performance, you should consider setting the
number of buffers to double that amount.

Even large CRDE applications are no likely to be larger than 200-250k
(with CRDE linked).  On a 640k machine that would leave some 300-400k
available to CRDE.  With that much available memory, you should have
no problem performing unlimited transactions on a tables with up to
5000 rows or more.



                     - 85 -






Chapter 15

Repairing Tables



Corrupted indexes

The most common type of corruption is when an index becomes corrupted.
This can happen when CRDE is updating the indexes on a table and
something unexpected happens.  Index corruption will usually show itself
as a incorrect query result or a "disk seek error", meaning that pointers
are out of alignment in the index file.  If you can open a table with
topen, and you're having problems, this is probably what is wrong.
You may also have trouble viewing the table if its index(es) are corrupt.


Testing your indexes

There is an easy way to test the integrity of an index visually by
using the tview function.  Simply call tview on the table using the
same sort order as the index in question.  For example, suppose you
wanted to test the integrity of an index on the "city" column (if one
existed) in sups.  First you would set up tview to view the table like

    tview(sups, NULL, "city", 3, 3, 78, 23, 0x07, 0x07, 0x07, NULL);

Scroll throughout the entire table.  If tview aborts prematurely, the
cursor gets stuck, or negative numbers appear in the row number column,
the index probably corrupt.  You can rebuild any suspect indexes with
the tindex function.  Many times, however, more than one index is
corrupt.  An easier way to rebuild your indexes is to go to DOS, delete
them, and then open the table with topen.  topen will automatically
rebuild all of the missing indexes for you.  This is convenient when
you may not remember all of the indexes on a table.


Repairing a table with trepair

Sometimes, however, the problem is much deeper than a corrupt index.
For example, you may not even be able to open the table with topen,
or when you do, your whole program blows up.  If topen refuses to
open the table, your only alternative is to attempt to repair the table
itself.  Fortunately, CRDE provides such a repair facility.  trepair
is a CRDE function declared in crde.h as

    TABLE *trepair(char *name, char *td);






                     - 86 -

name is the name of the table to be repaired.  td is a table descriptor
which must exactly describe the structure of the original table.
trepair uses this model to rebuild the table.  trepair will attempt to
recover as much of the table and primary index as it possibly can.
trepair does not repair secondary indexes.  However, once the table is
repaired, you can simply rebuild them with the tindex function.


Limitations of trepair

trepair can repair most logically damaged table without a hitch.
Logically damaged tables are those which contain incorrect or outdated
information.  Usually this type of corruption occurs when all unwritten
information was not committed to disk, such as during a power-outage
or similar catastrophe.  trepair can even fix certain types of
physically damaged tables.  Physically damaged tables are those which
have defective sectors or some other type of physical damage (the table
was partially overwritten or truncated).  However, if your table is
physically damaged, your problems may be deeper than just a corrupted
table.  Your disk may need to be reformatted or your disk drive repaired.
trepair however, is fairly limited in the amount of physical damage it
can repair.  trepair cannot unerase tables nor fix bad sectors or
clusters in a file.  In addition, trepair must be able to open a table
to repair it.


































                     - 87 -






Reference Manual



This chapter contains a detailed description of all the functions in
the CRDE library.

The following sample library look-up entry explains how to use this
portion of the Referance Manual to reference CRDE library functions.


Name            The function's name.

Description      Simple explanation of what the function does.

Declaration      Function's full standard C declaration.

Remarks         A detailed explantion of how a function works, how to
        use it, what its used for, and any other details
        relating to the function.

Return Value    Possible return values for the function.

See Also        Related functions/topics.

Example          An example demonstrating how to use the function.

Output           Output of the example program, if any.


Standard conventions in CRDE

The CRDE function library was designed to be consistant in the way
that functions return values and accept parameters.   Below are some
basic rules common to all CRDE functions.


Return values

Every CRDE function returns some type of error indicator in the event
of an error.  The type of indicator depends upon the return type of
the function.











                     - 88 -

Return type        Possible return values

TABLE *         Returns a pointer to an open table if successful, or
        NULL if an error occurs.

int         Returns 0 if successful, or a negative error code if
        an error occurs.

long              Returns the number of rows involved in the operation,
        or a negative error code if an error occurs.

char              Returns 0 on error.


Note:  All of CRDE's error codes are negative integers.  In addition,
CRDE sets the global variable terrno to the appropriate error code
whenever an error occurs.  If the function does not return an integer
type, you can use terrno to determine the cause of the error.  If the
function does return an integer type then terrno will match the error
code returned by the function.  CRDE also has another global variable
called trowsfound.  trowsfound contains the number of rows (if any)
involved in the last operation.  Like terrno, trowsfound can be used to
determine the number of rows affected by the last operation if the
function does not return an integer value.  In the event of an error,
trowsfound will hold the number of rows affected before the error occured.


Functions which return a TABLE *

Every CRDE function which returns a TABLE * accepts a char *name as
its first parameter.  name may be any of three values:

    TEMP        The table is a TEMP table.
    STATIC       The table is a STATIC table.

If a table name is not TEMP or STATIC, it is assumed to be an asciiz
string containing the table's base name.  The string maybe any legal
DOS pathname up to 64 characters in length.  It may not include
wildcards or an extension.  Any table previously existing by that name
is automatically overwritten by the new table.  If a table is TEMP or
STATIC, it is known as a temporary table.  Otherwise the table is a
permanent table.

Some functions return a table whose structure is identical to the one
passed to it as a parameter.  Some examples are tcopy, tborrow, and
tselect.  What this means is that the resulting table will have the
same column names, types, and primary key as the original table.
Secondary indexes,  however, are not considered as part of a table's
structure and are never inherited by the result table.

Several functions which accept two tables as parameters require that
both tables be compatible.  Examples are tunion, tdiff, and tintersect.
Two tables are compatible if they have identical column types and sizes
on a column by column basis.  They may have different column names, and/
or a different primary key and secondary keys.



                     - 89 -

In some functions, whether or not a table has a primary key can have
an affect on how the function works.  Such variations are noted in the
reference guide.


Parameters in CRDE functions

When at all possible (in the Reference Manual and in crde.h), function
prototypes use symbolic parameter names to help identify the meaning of
the parameter.  Those parameters and their meanings are listed below.

Paramter name    Meaning

name        The name parameter is the first parameter of any
        CRDE function returning a TABLE *.  name may be
        TEMP, STATIC, or a pointer to a legal DOS
        pathname in asciiz format.  The pathname may not
        contain wildcards or an extension.

t, t1, t2, etc.    These parameters are tables passed to the function.

c        This parameter expects a pointer to an asciiz string
        containing a column name  like "cust#".

cl        This parameter expects a pointer to a asciiz string
        containing a list of column names seperated by
        whitespace.

td        This parameter expects a table descriptor which is
        very similar to the column list above.  A table
        descriptor also contains data type information.  Any
        column preceded with an asterisk '*' are part of the
        table's primary key.

id        This parameter expects an index descriptor similar to
        the column list above.  However, any columns
        preceded with and exclaimation mark '!' are indexed in
        descending order.

pl        This parameter expects a special type of column list.
        Columns in the list may be preceded with an asterisk
        '*', marking them as part of a primary key.  It is
        different from a table descriptor because no data type
        information is required.


Auto-cleanup of TEMP tables

When a TEMP table is passed to a CRDE function, it may be automatically
dropped when the function completes.  This feature allows you to nest
functions which return tables inside one another safely.  The general
rule is:






                     - 90 -

    A CRDE function will automatically drop a TEMP table
    passed to  it as a parameter if the function does not or can not
    alter the table in any way. If the TEMP table cannot be thus
    affected by the function, it will be automatically dropped when
    the function  completes, even in the event of an error.

Some CRDE functions will not automatically drop a table, regardless of
its type. These functions are:  ttransact, tcommit, trollback, tmark,
twritec, tnormal, tmode.

















































                     - 91 -

----------------------------------------------------------------
day
----------------------------------------------------------------

Description    Determines calender day of a date_t value.

Declaration    int day(date_t d);

Remarks        day returns a value between 1 and 31.

Return Value    day returns the calender day of the specified date,
        or -1 if the date is invalid.

See Also    month, year

Example        #include "date.h"

        int printdate(date_t d);
        {
          printf("%d/%d/%d",  month(d), day(d), year(d));
        }



----------------------------------------------------------------
dayofweek
----------------------------------------------------------------

Description    Determines the day of week of a particular date.

Declaration    int dayofweek(date_t d);

Remarks        dayofweek returns a value from 0 to 6 correponding the
        day of week, Sunday to Saturday, of the specified date.
        date.h provides 7 constants analogous to the return
        value of the dayofweek function.

        #define SUNDAY        0
        #define MONDAY        1
        etc...
        #define SATURDAY    6

Return Value    dayofweek  returns a value from 0 to 6, corresponding
        to the day of week, or -1 if the date is invalid.

See Also    firstday.

Example        #include "date.h"

        /* functions to determine whether a date is on a weekend
        or weekday */







                     - 92 -

        int weekend(date_t d)
        {
          int i;

          i = dayofweek(d);
          return i == SATURDAY || i == SUNDAY;
        }

        int weekday(date_t d)
        {
          return !weekend(d);
        }



----------------------------------------------------------------
dayofyear
----------------------------------------------------------------

Description    Determines the day of year of some calender date.

Declaration    int dayofyear(int month, int day, int year);

Remarks        dayofyear returns a value between 1 and 366 where
        January 1 is day 1, January 2 is day 2, February 1 is
        day 32, etc.

Return Value    Returns the day of year, or -1 if the date is invalid.

See Also    monthday.

Example        #include <stdio.h>
        #include "date.h"

        int main()
        {
          char buf[16];
          int m, d, y;

          printf("Enter date: ");
          scanf(" %d / %d / %d", &m, &d, &y);

          printf("Days since Jan 1 is %d.\n", dayofyear(m, d, y));
        }


Output        Enter date: 2/1/1990
        Days since Jan 1 is 32.










                     - 93 -

----------------------------------------------------------------
daysinyear
----------------------------------------------------------------

Description    Returns number of days in a year.

Declaration    int daysinyear(int year);

Remarks        daysinyear returns value of 365 or 366 depending upon
        whether the year is a leap year or not.

Return Value    daysinyear returns the number of days in a year.

See Also    leapyear.

Example        #include <stdio.h>
        #include "date.h"

        int main()
        {
          printf("Days in the year 1990 = %d.\n",
          daysinyear(1990));

          printf("Days in the year 2000 = %d.\n",
          daysinyear(2000));
        }

Output        Days in the year 1990 = 365.
        Days in the year 2000 = 366.



----------------------------------------------------------------
firstday
----------------------------------------------------------------

Description    Returns date of first day of week in a particular month.

Declaration    date_t firstday(int month, int year, int dayofweek);

Remarks        firstday returns a date, as a date_t value, for the
        first dayofweek in that month and year.  For example,
        to get the date of the first Monday of August 1990
        would look like

            date_t firstmon;

            firstmon = firstday(6, 1990, MONDAY);

        MONDAY is a constant defined in date.h specifying the
        day of the week.







                     - 94 -

        firstday can also be used to calculate the second,
        third, and fourth occurances of a date by simply adding
        the appropriate multiple of 7 to the result.  The
        second Monday in June 1990 would be

            firstday(6, 1990, MON) + 7;

Return Value    firstday returns a date_t value or -1 if the date is
        invalid.

See Also    dayofweek.

Example        /*  function to determine if today is a payday (the
        second and fourth Fridays of every month). */

        int PayDay(date_t today)
        {
          int m, y;
          date_t d;

          m = month(today); y = year(today);
          d = firstday(m, y, FRIDAY);

          return (today == d+7) || (today == d+21);
        }



----------------------------------------------------------------
gmdate
----------------------------------------------------------------

Description    Converts a date_t value into a calender date.

Declaration    int gmdate(date_t d, int *month, int *day, int *year);

Remarks        gmdate converts the date_t value d into its calender
        definition, and stores the results in month, day, and
        year.

Return Value    gmdate returns 0 if successful, or -1 if the date was
        invalid.

See Also    mkdate.

Example        /* function to return the first day of the month of the
        current date */

        date_t FirstOfMonth(date_t dt)
        {
          int m, d, y;

          gmdate(dt, &m, &d, &y);
          return mkdate(m, 1, y);
        }



                     - 95 -

----------------------------------------------------------------
leapyear
----------------------------------------------------------------

Description    Determines if a year is a leap year.

Declaration    int leapyear(int year);

Remarks        leapyear recognizes both leap years and leap centuries.
        A year is a leap year is it is evenly divisible by 4,
        excluding those centuries (1800, 1900, 2100, etc.) not
        evenly divisible by 400.

Return Value    leapyear returns 1 year is a leap year, or 0 if it is not.

See Also    daysinyear.

Example        #include <stdio.h>
        #include "date.h"

        int main()
        {
          int i;

          printf("Enter year: ");
          scanf(" %d", &i);
          if (leapyear(i))
            printf("%d is a leap year.\n", i);
          else
            printf(%d is not a leap year.\n", i);
        }



----------------------------------------------------------------
mkdate
----------------------------------------------------------------

Description    Converts a calender date to a date_t value.

Declaration    date_t mkdate(int month, int day, int year);

Remarks        mkdate converts the calender date month, day, year
        into its date_t equivelent.  mkdate knows about the
        days in    months and years, and about leap years, and
        will return -1 if the date is invalid, for example
        2/29/1990.

        mkdate can convert dates from 1/1/1 to 12/31/32767.

        A common error when using mkdate is to forget to
        indicate the correct century in the year, for exmaple

            mkdate(4, 17, 90)

        specifies the date 4/17/0090 not 4/17/1990.


                     - 96 -

Return Value    mkdate returns a date_t value or -1 if the date is
        invalid.

See Also    gmdate.

Example        #include <stdio.h>
        #include "date.h"

        int main()
        {
          date_t today;
          int m, d, y;

          do {
            printf("Enter today's date: ");
            scanf(" %d / %d / %d", &m, &d, &y);
            today = mkdate(m, d, y);
          } while (today < 0);

          gmdate(today + 7, &m, &d, &y);
          printf("Date a week from now = %d/%d/%d.\n", m, d, y);
        }



----------------------------------------------------------------
month
----------------------------------------------------------------

Description    Returns calender month of a date_t value.

Declaration    int month(date_t d);

Remarks        month returns a value between 1 and 12.

Return Value    month returns the month of the date or -1 if the
        date is invalid.

See Also    day, year.

Example        See day.



----------------------------------------------------------------
monthadd
----------------------------------------------------------------

Description    Performs month addition/subtraction on a date.

Declaration    date_t monthadd(date_t d, int months);







                     - 97 -

Remarks        monthadd adds the specified number of months to a date
        creating a new date.  monthadd knows about calender
        dates and leap years, and correctly adds the number of
        months specified.  For example

            monthadd(mkdate(2, 1, 1992), 1);

        correctly yields 3/1/1992.

        You can subtract months from a date by simply specifying
        a negative value for months.

Return Value    monthadd returns a date_t value, or -1 if the date is
        invalid.

See Also    yearadd.

Example         #include "date.h"

        /* function to determine if an payment was received
        within a month of its due date.  If not, a surcharge
        will be added to the amount owed */

        int ReturnBy(date_t due, date_t received)
        {
          return received <= monthadd(due, 1);
        }



----------------------------------------------------------------
taccess
----------------------------------------------------------------

Description    Determines the accessibility of a table.

Declaration    int taccess(char *name, int mode);

Remarks        taccess tests a file for certain accessibility rights.
        mode determines the type of test which will be done.
        When mode is 0 taccess tests for existance of a file.
        For example, to see if the "sups" table had been created

            TABLE *sups;

            if (taccess("sups", 0) == 0)
              /* file exists so open it */
              sups = topen("sups");
            else
              /* file does not exist, create new */
              sups = tcreat("sups", "*s# i, sname c26, city"
                "c26, status i");

        taccess returns 0 if the tables exists.  You can test
        for write permission to a table by setting mode to 2.



                     - 98 -

Return Value    taccess returns 0 if access rights tested positive, or
        -1 if not.



----------------------------------------------------------------
tadd
----------------------------------------------------------------

Description      Inserts records from one table into another.

Declaration      long tadd(TABLE *t1, TABLE *t2);

Remarks         tadd inserts the records in t2 into t1.  If t1 is
        keyed, only those records which do not violate the
        primary key will be inserted.

        If t2 is a TEMP table, t2 is automatically dropped
        by tadd, even if an error occurs.  Otherwise, t2 is
        unaffected by tadd.

Return Value     tadd returns the number of records added if successful,
        or an error code if an error occured.

See Also         tupdate, tsubtract.

Example        /* function to combine orders made during a month to a
        history file containing all previous orders.  Orders
        table and History table are assumed to have compatible
        structures. */

        int SaveOrders(TABLE *orders, TABLE *history)
        {
          long i;

          i = tadd(history, orders);
          if (i > 0)
            /* if orders successfully added then clear
            orders table for next month */
            tempty(orders);
          return i > 0 ? 0 : i;
        }



----------------------------------------------------------------
tall
----------------------------------------------------------------

Description      Performs a (user-defined) function on all open tables.

Declaration     int tall(int (*func)(TABLE *));






                     - 99 -

Remarks         tall performs a function, accepting a TABLE * as a
        parameter, returning an int, on all currently open
        tables (temporary and permanent).  CRDE keeps an
        active list of all open tables during the course of
        a program's execution.  tall simply traverses this
        list, performing func on each table.  tall is designed
        to work even with functions which alter the table list
        such as topen and tclose.  tall will only perform func
        on every table which was open immediately prior to
        calling tall.  This allows tall to work with functions
        like tclose, and also prevents it from working
        recursively on functions which open tables.

        tall will work directly with many CRDE functions such
        as:  tclose, tdrop, tempty, ttransact, trollback,
        tcommit, twritec, tnormal, tflush, and trelease.

Return Value     tall returns the first non-zero result of func,
        otherwise it returns 0.

Example #1         #include <stdio.h>
              #include "crde.h"

        int tbuffers = 128;

              int main()
              {
          TABLE *sups, *parts, *orders;

          /* open some tables */
          sups = topen("sups");
          parts = topen("parts");
          orders  = topen("orders");

          /* put all in writec mode */
          tall(twritec);

          /*
            rest of user-program here...
          */

          /* closes all tables */
          tall(tclose);
        }

Example #2       #include <stdio.h>
        #include "crde.h"

        /* function which closes all temporary tables */









                    - 100 -

              int tdroptemp(TABLE *t);
              {
          int i;

          i = tmode(t);
          return (i == T_TEMP || i == T_STATIC) ? tdrop(t) : 0;
        }

              int tdroptempall()
              {
          return tall(tdroptemp);
        }



----------------------------------------------------------------
taverage
----------------------------------------------------------------

Description      Calculates the average of a column.

Declaration      int taverage(TABLE *t, char *c, void *result);

Remarks          taverage calculates the average of a column in a
        table and places the result in the buffer pointed
        to by result.  c must be acolumn in the table.

        taverage only works with types i, l, f, and $.  The
        type of buffer pointed to by  result is always double
        for taverage, regardless of the column type.

        The value in result is undefined if an error occurs.

        If t is a TEMP table, it will automatically be dropped
        by taverage, even if an error occurs.  Otherwise, t is
        unaffected by taverage.

Return Value     taverage returns 0 in successful, or an error code if
        an error occured.

See Also         tcount, tmax, tmin, tsum.

Example          #include <stdio.h>
              #include "crde.h"

        int tbuffers = 128;

              int main()
              {
          TABLE *sups, *answer;
          double avg;

          sups = topen("sups");





                    - 101 -

          /* query all suppliers with above average status */

          taverage(sups, "status", &avg);
          answer = tselect(TEMP, sups, "status", GT, (int)avg,
            NULL);

          tview(answer, NULL, NULL, 3,3, 78, 23, 0x07, 0x07,
            0x07, NULL);

          tall(tclose);
        }



----------------------------------------------------------------
tborrow
----------------------------------------------------------------

Description     Creates a new (empty) table like another table.

Declaration      TABLE *tborrow(char *name, TABLE *t);

Remarks          tborrow creates a new (empty) table with the exact
        same structure t.  The result table will have same
        columns and primary key as t.  However, any secondary
        keys are not copied by tborrow.

        If t is a TEMP table, it is automatically dropped by
        tborrow, even if an error occurs.  Otherwise, t is
        unaffected by tborrow.

Return Value     tborrow returns a pointer to an open table if
        successful, or NULL if an error occured.

See Also         tcopy.

Example        #include <stdio.h>
        #include "crde.h"

        int tbuffers = 128;

        int main()
        {
          TABLE *sups, *saved;

          /* program to remove some suppliers from the Sups
          table and place them in a special achive table named
          Saved */

          sups = topen("sups");
          saved = tborrow("saved", sups);

          tadd(saved, tselect(TEMP, sups, "status", LT, 5, NULL);
          tdelete(sups, "status", LT, 5, NULL);




                    - 102 -

          tadd(saved, tselect(TEMP, sups, "status", GT, 25, NULL);
          tdelete(sups, "status", GT 25, NULL);

          tclose(sups);
          tclose(saved);
        }



----------------------------------------------------------------
tchange
----------------------------------------------------------------

Description      Changes rows in a table.

Declaration      long tchange(TABLE *t, ...);

Remarks         tchange accepts two NULL terminated argument lists,
        the first, a list of the changes to be made, followed
        by a search expression to determine which of the
        records to change.

        A change list consists of zero or more changes.  A
        change consists of a column name followed by a value.

        A typical tchange call will have the format:

        tchange(t, chcol1, chval1, chcol2, chval2, ..., NULL,
          colname1, relop1, value1, ..., NULL);

        chcol[n] can be any valid column name of t.

        chval[n] may be either a constant or a variable.  The
        type of chval[n] must be exactly the type of chcol[n].
        The value of chcol[n] is changed to chval[n] in every
        record selected.

        The search expression is identical to the one described
        in tselect.  Only records which match the search
        expression are changed.

              Here are some examples:

              /* set all quantities in a orders table to 0 */
              tchange(orders, "qty", 0, NULL, NULL);

              /* change credit of a single customer */
              int newquant, custno;
              ...
        tchange(cust, "qty", newquant, NULL, "cust#", EQ, custno,
          NULL);

        tchange accepts a maximum of 16 changes.  It is not an
        error to specify no changes, but doing so will have no
        effect on the table.



                    - 103 -

Return Value     tchange returns the number of rows changed if
        successful, or an error code if an error occurred.

See Also         tchangeif, tselect.

Example     /* routine to change a supplier id # while maintaining
        referential integrity */

        int ChangeSup(int oldsup, int newsup)
        {
          int i;

          i = tchange(sups, "s#", newsup, NULL, "s#", EQ,
            oldsup, NULL);
          if (i > 0)
            i = tchange(orders, "s#", newsup, NULL, "s#", EQ,
              oldsup, NULL);
          return i;
        }



----------------------------------------------------------------
tchangeif
----------------------------------------------------------------

Description        Changes rows in a table.

Declaration      long tchangeif(TABLE *t, int (*action)(void *), ...);

Remarks          tchangeif is exactly like tchange except it accepts an
        additional parameter action.  Every row which matches
        the search expression is passed to action for further
        evaluation.  action should behave exactly as the action
        described in tselectif:

              Result of action     What happens

            > 0        Row is changed.

                 = 0              Row is not changed.

            < 0              An error occurred.  tchangeif
                    is aborted and returns the
                    result of action.

              See tselectif for more details on action.

        tchangeif differs from the other "..if functions" in
        one small detail.  Any changes to the row passed to
        action are made permanent along with any changes
        listed in the change list (if, of course, action
        returns an integer > 0.)  Note:  any changes listed
        in the change list are made after the call to action,
        and so will override any changes made inside of action
        itself.


                    - 104 -

        For example to give everyone in an employee table a 5%
        salary increase:

        TABLE *emps;

              int increase(void *rec)
              {
          /* user-defined if function - note that it is
          necessary to cast void * rec. */

          ((emp *)rec)->salary = ((emp *)rec)->salary * 0.05;

          return 1;
        }
        ...
        tchangeif(emps, increase, NULL, NULL);

Return Value     tchangeif returns the number of records changed if
        successful, or an error code if an error occurred.

See Also         tchange, tselect, tselectif.



----------------------------------------------------------------
tchcol
----------------------------------------------------------------

Description     Renames a column.

Declaration      int tchcol(TABLE *t, char *old, char *new);

Remarks          tchcol renames column old as new.  old must be a valid
        column name in t, and new must be an unique to t.  The
        position, datatype, and primary key of the column
        remain the same.

        A transaction keeps track of any columns which are
        renamed, so rolling back a transaction will cause any
        columns which were renamed during the transaction to
        be reversed.

        You may not use tchcol on a table which is in read-
        only mode.  See tselectif for details.

Return Value     tchcol returns 0 if successful, or an error code if an
        error occurred.

Example        #include "crde.h"

        int tbuffers = 128;







                    - 105 -

        int main()
        {
          TABLE *t;

          /* program to change the sups column "sname" to "name" */

          t = topen("sups");
          tchcol(t, "sname", "name");
          tclose(t);
        }



----------------------------------------------------------------
tclose
----------------------------------------------------------------

Description      Closes a table.

Declaration      int tclose(TABLE *t);

Remarks          tclose closes a table and all its associated indexes.
        Unless the table is a temporary table, all unwritten
        buffers are flushed to disk before closing.  Likewise,
        any transaction pending on the table is automatically
        commited before closing.  All memory held by table is
        released upon closing.  If the table is a temporary table
        (TEMP or STATIC) it is deleted upon closing.  There is
        no difference between closing and dropping a temporary
        table.

        A program should always close all tables before
        completing.  A simple way to insure this is to issue a

            tall(tclose);

              at the end of a program.

        tclose closes a table even if an error occurs.

Return Value     tclose returns 0 on success, or an error code if an error
        occured.

See Also         tdrop, topen.

Example        #include "crde.h"

        int tbuffers = 128;

        int main()
        {
          TABLE *sups, *parts, *orders;

          sups = topen("sups");
          parts = topen("parts");
          orders = topen("orders");


                    - 106 -

          /* main part of program here... */

          tclose(sups);
          tclose(parts);
          tclose(orders);
        }



----------------------------------------------------------------
tcmpstruct
----------------------------------------------------------------

Description      Compares two table's structures.

Declaration      int tcmpstruct(TABLE *t1, TABLE *t2);

Remarks          tcmpstruct compares the tables on a column by column
        basis, for identical types and sizes and primary key.
        Column names are not considered.  tcmpstruct returns
        one of the following:

              Return value        Meaning

            0        Tables are different.

               1            Tables have same structures but
                    different primary keys.

               2            Tables are identical.

        Whether or not either table has any secondary keys is
        not considered by tcmpstruct.

Return Value     tcmpstruct returns one of the return values above or an
        error code if an error occurred.

Example        #include <stdio.h>
        #include "crde.h"

        int tbuffers = 128;

        int main()
        {
          TABLE *t1, *t2;

          t1 = topen("sups");
          t2 = tborrow(STATIC, t1);

          switch (tcmpstruct(t1, t2)) {
            case 0 :
              printf("Tables are not compatible.\n");
              break;





                    - 107 -

            case 1 :
              printf("Tables are compatible but with "
            "different primary keys.\n");
              break;
            case 2 :
              printf("Tables are identical.\n");
              break;
            default :
              printf("Error %d occurred.\n", terrno);
          }
        }



----------------------------------------------------------------
tcols
----------------------------------------------------------------

Description      Returns the number of columns in a table.

Declaration      int tcols(TABLE *t);

Remarks          tcols returns a value from 1 to 256.

Return Value    tcols returns the number of columns in a table if
        successful, or an error code if an error occurred.

See Also         trows.

Example        #include <stdio.h>
        #include "crde.h"

        int tbuffers = 128;

        int main()
        {
          TABLE *t;

          t = topen("sups");

          printf("Sups table has %d columns.\n", tcols(t));
          tclose(t);
        }



----------------------------------------------------------------
tcolsize
----------------------------------------------------------------

Description      Returns the size of a column.

Declaration      int tcolsize(TABLE *t, char *c);

Remarks          tcolsize returns a value from 1 to 256, the size of
        the column in bytes.


                    - 108 -

Return Value     tcolsize returns the size of a column if successful,
        or an error code if an error occurred.

See Also         tcoltype, trowsize.

Example        #include "crde.h"

        int tbuffers = 128;

        int main()
        {
          TABLE *t;

          t = topen("sups");

          printf("Size of 'sname' column in Sups table is %d "
            "bytes.\n", tcolsize(t, "sname"));

          tclose(t);
        }



----------------------------------------------------------------
tcoltype
----------------------------------------------------------------

Description      Returns a column's type.

Declaration      char tcoltype(TABLE *t, char *c);

Remarks          tcoltype will return the character mnemonic for a
        column's datatype (either c, i, l, d, f, or $).  To
        determine the size of a column, use tcolsize.

Return Value     tcoltype returns the type of the column as a one
        character code if successful, or 0 if an error occured.

See Also         tcolsize.

Example        #include <stdio.h>
        #include "crde.h"

        int tbuffers = 128;

        int main()
        {
          TABLE *t;

          t = topen("sups");
          printf("Type of 'sname' column in Sups is ");
          switch (tcoltype(t)) {
            case 'c' : puts("char"); break;
            case 'i' : puts("int"); break;
            case 'l' : puts("long"); break;



                    - 109 -

            case 'd' : puts("date"); break;
            case 'f' : puts("double"); break;
            case '$' : puts("dollar"); break;
          }
          tclose(t);
        }



----------------------------------------------------------------
tcommit
----------------------------------------------------------------

Description      Makes a transaction permenent.

Declaration      int tcommit(TABLE *t);

Remarks          tcommit makes a transaction on a table permanent by
        saving all the changes made during the transaction to
        disk.

        tcommit will commit the transaction even if an error
        occurs.  In any event, the transaction is considered
        over.  To begin a new transaction you must call
        ttransact again.

        Calling tcommit outside a transaction has no effect
        on a table.

Return Value     tcommit returns 0 if successful, or an error code if
        an error occurred.

See Also         trollback, ttransact.

Example        #include <stdio.h>
        #include <stdlib.h>
        #include "crde.h"

        int tbuffers = 128;

        int main()
        {
          TABLE *t;

          t = topen("sups");

          /* begin transaction */
          ttransact(t);

          MakeChanges(t);

          printf("Save changes (y/n)? ");
          if (toupper(getchar()) == 'Y')
            tcommit(t);




                    - 110 -

          else
            trollback(t);

          tclose(t);
        }



----------------------------------------------------------------
tcopy
----------------------------------------------------------------

Description      Copies a table.

Declaration      TABLE *tcopy(char *name, TABLE *t);

Remarks          tcopy creates a copy the table and its primary key.
        Any secondary keys are not copied however.

        If t is a TEMP table, it will automatically be
        dropped by tcopy, even if an error occurs.  Otherwise,
        t is unaffected by tcopy.

Return Value     tcopy returns a pointer to an open table if successful,
        or an error code if an error occurred.

See Also         tborrow.

Example        #include <stdio.h>
        #include "crde.h"

        int tbuffers = 128;

        int main(int argc, char *argv[])
        {
          /* program to make a backup of the table specified at
          the command line; if backup name not supplied then
          "backup" is used */

          TABLE *t1, *t2;
          char dest[64];

          /* no argmuents, display help message */
          if (argc < 2) {
            printf("You must specify a table to backup.\n");
            exit(1);
          }

          /* get destination name */
          if (argc > 2)
            strcpy(dest, argv[2]);
          else
            strcpy(dest, "backup");





                    - 111 -

          /* open source and backup */
          if ((t1 = topen(argv[1])) != NULL) {
            if ((t2 = tcopy(dest, t1)) != NULL) {
              tclose(t2);
              printf("Backup to \"%s\" successful.\n", dest);
            }
            else
              printf("Error occurred making backup "
            "(error = %d).\n", terrno);

            tclose(t1);
          }
          else
            printf("Unable to open source file.\n");
        }



----------------------------------------------------------------
tcount
----------------------------------------------------------------

Description      Counts the number of values in a column.

Declaration     int tcount(TABLE *t, char *c, void *result);

Remarks          Because CRDE does not support NULL values, this
        function is equivelent to calling trows.  The
        result for tcount is always long, regardless of
        the column type.

        result is undefined is an error occurs.

        If t is a TEMP table, it will automatically be
        dropped by tcount, even if an error occurs.
        Otherwise, t is unaffected by tcount.

Return Value     tcount returns 0 if successful, or an error code
        if an error occurred.

See Also         taverage, tmax, tmin, tsum.

Example        #include "crde.h"

        int tbuffers = 128;

        int main()
        {
          TABLE *t;
          long l;
          int i;

          t = topen("sups");
          i = tcount(t, "s#", &l);
          if (i < 0)
            printf("Error %d occurred.\n", i);


                    - 112 -

          else
            printf("Table contains %ld suppliers.\n", l);
          tclose(t);
        }



----------------------------------------------------------------
tcreat
----------------------------------------------------------------

Description      Creates a new table.

Declaration      TABLE *tcreat(char *name, char *td);

Remarks         tcreat creates a table named name, overwriting any
        existing table by that name.  The table's structure
        is specified by the table descriptor td, an asciiz
        (null- terminated) string containing a list of column
        names and column types.  Both column names and types
        must be seperated by whitespace.  Whitespace characters
        are all those characters classified by the standard
        library function isspace() plus the comma ','.  All
        other characters (including graphical symbols) are
        significant.  Case is also significant, so Cust# and
        cust# are considered different.  This is important since
        every column name in a table must be unique.

        An example tcreat call might look like

        t = tcreat("sups", "*s# i, sname c26, city c26, status i");

        Every column name must be followed by a datatype.
        CRDE provides six basic datatypes common to virtually
        all database managers. In addition, all datatypes are
        analogous to native C datatypes which makes for easy
        transition from CRDE tables to C variables.

        Here is a list of the datatypes supported by CRDE each
        followed by a description of the datatype, and its C
        equivelent:

              Type    Description        C equivelent

              cXXX      array of up to 256    char[ XXX]
                characters. XXX is a
            positive number
            between 1 and 256.

              i         integer (whole         int
            numbers,  + and -)

        l         long integer               long int

        d        date                       1date_ t



                    - 113 -

        f         floating-point             double

        $         dollar value               1dollar_t


        1Date and dollar types have no direct C equivelents
        but are represented as C types by the typedefs date_t
        and dollar_t defined in CRDE.  Typical representations
        are long int for date_t and double for dollar_t
        although you should always use the date_t and
        dollar_t symbols for portability reasons.


        Defining a primary key.

        Any column name directly preceded with the '*'
        character is considered to be part of the primary
        key. There must be no whitespace between the '*' and
        the column name.  Up to 8 columns may be part of the
        primary key.  Although CRDE tables are not required
        to have a primary key, it is generally good relational
        practice to do so.  Defining a primary key on a table
        prevents two rows in a table which have identical values
        in every column that is marked with an asterisk '*'.  In
        addition, defining a primary key may have performance
        benefits as well (see Chapter 3, Creating Tables).
        However there is a catch:  a primary key makes a table
        larger, using up more disk space.

Return Value     tcreat returns a pointer to an open table if successful,
        or NULL if an error occurs.

See Also        topen.

Example:          #include <stdio.h>
              #include "crde.h"

              typedef struct {
          int no;
          char name[26];
          int age;
          dollar_t salary;
        } emp;

        int tbuffers = 128;

              int main()
              {
          TABLE *t;
          emp e;

          /* create a simple employee table */
          t = tcreat("emps", "*e# i, name c26, age i, salary $");





                    - 114 -

          /* add a record to the table */
          e.no = 1;
          strcpy(e.name, "John Smith");
          e.age = 35;
          e.salary = 33000.0;

          tinsert(t, &e, 1);

          /* close the table */
          tclose(t);
        }



----------------------------------------------------------------
tcrstruct
----------------------------------------------------------------

Description      Creates a table from struct table.

Declaration      TABLE *tcrstruct(char *name, TABLE *t);

Remarks          Instead of using a table descriptor to create a new
        table, tcrstruct uses a table containing column name
        and type information similar to the one generated by
        tstruct.

        The only requirements which tcrstruct makes of the
        struct table t is that its first two columns be of
        type c (char).  The first column should hold the
        column names while the second should hold the column
        type.  Other than that, there are no other restrictions
        on the structure of t.

        The columns in the new table will appear in the same
        order that they were physically inserted in the struct
        table.

        tcrstruct if useful in that it can be used by programs
        to create table structures dynamically or even
        interactively.

        If t is a TEMP table, it will be automatically dropped
        by tcrstruct, even if an error occurs.  Otherwise, t is
        unaffected by tcrstruct.

Return Value     tcrstruct returns a pointer to an open table if
        successful, or NULL if an error occurred.

Example          #include <stdio.h>
              #include "crde.h"

        int tbuffers = 128;





                    - 115 -

              int main()
              {
          TABLE *t, *struc;
          char name[64], cname[32], ctype[5];

          struc = tcreat(STATIC, "*colname c18, coltype c5");

          if (struc == NULL) {
            printf("Unable to create struct table.\n");
            exit(1);
          }

          printf("Table name: ");
          gets(name);
          while (1) {
            printf("\nColumn name: ");
            gets(cname);
            if (strcmp(cname, "") == 0)
              break;
            printf("Column type: ");
            gets(ctype);
            tload(struc, cname, ctype);
          }

          t = tcrstruct(name, struc);
          if (t == NULL) {
            printf("Error: unable to create new table.\n");
            exit(1);
          }

          printf("\nTable \"%s\" created.\n", name);
          printf("Table has %d columns totalling %d bytes in "
            "size.\n", tcols(t), trowsize(t));

          if (tkeyed(t))
            printf("Table is keyed.\n");
          else
            printf("Table is not keyed.\n");

          tclose(t);
          tdrop(struc);
        }

Output:           Table name: dummy

              Column name: *cust#
              Column type: i

              Column name: name
              Column type: c26

              Column name: phone
              Column type: c13

              Column name:



                    - 116 -

              Table "dummy" created.
        Table has 3 columns totalling 41 bytes in size.
        Table is keyed.



----------------------------------------------------------------
tdelete
----------------------------------------------------------------

Description      Deletes records from a table.

Declaration      long tdelete(TABLE *t, ...);

Remarks          tdelete expects a series of arguments which form a
        search expression like the one described in tselect.
        Any records matching the search expression are deleted
        from the table.

        Note:  tdelete does not physically delete rows from
        a table.  Rather it marks the space as unused, and is
        reclaimed automatically when you re-insert rows into
        the table.

Return Value     tdelete returns the number of rows deleted if successful,
        or an error code if an error occurred.

See Also         tdeleteif, tinsert, tselect.

Example        int DeleteCust(int supno)
        {
          /* delete a supplier from the sups table; deletes any
          orders made by that supplier to maintain referential
          integrity */

          int i;

          i = tdelete(sups, "s#", EQ, supno, NULL);
          if  (i > 0)
            i = tdelete(orders, "s#", EQ, supno, NULL);
          return i;
        }



----------------------------------------------------------------
tdeleteif
----------------------------------------------------------------

Description      Deletes records from a table.

Declaration      long tdeleteif(TABLE *t, int (*action)(void *), ...);






                    - 117 -

Remarks          tdeleteif is exactly like tdelete except it accepts an
        additional parameter action.  Every row which matches
        the search expression is passed to action for further
        evaluation.  action should behave exactly as the action
        described in tselectif:

        Result of action    What happens

            > 0              Row is deleted.

                 = 0              Row is not deleted.

            < 0              An error occurred.
                    tdeleteif is aborted and
                    returns the result of action.

              See tselectif for more details on action.

Return Value     tdeleteif returns the number of rows deleted if
        successful, or an error code if and error occurred.

See Also         tdelete, tselect, tselectif.

Example        /* routine to delete sups who live in either Atlanta
        or Boston */

        int DeleteSups(void *rec)
        {
          return (strcmp(((sup *)rec)->city, "Boston) == 0 ||
            strcmp(((sup *)rec)->city, "Atlanta") == 0;
        }

        tdeleteif(sups, DeleteSups, NULL);



----------------------------------------------------------------
tdiff
----------------------------------------------------------------

Description      Returns the relational difference of two tables.

Declaration      TABLE *tdiff(char *name, TABLE *t1, TABLE *t2);

Remarks          tdiff returns the relational difference of tables
        t1 and t2.  The result table will have a structure
        identical to t1 and contain all the records found
        in t1 not found in t2.

        tdiff requires that t1 and t2 be compatible, i.e.
        that they be equivelent on a column by column basis
        in both type and size.  However they may have
        different primary or secondary keys.  tdiff works
        differently depending upon whether t1 is keyed or
        not.  If t1 is keyed then tdiff will return every



                    - 118 -

        row in t1 whose key values does not match the key
        values of any row in t2.  If t1 is not keyed, then
        only those rows which do not entirely match any row
        in t2 will be returned.

        If t1 or t2 is a TEMP table, then tdiff will drop
        either or both of them, respectively.  Otherwise, t1
        and t2 are unaffected by tdiff.

Return Value     tdiff returns a pointer to an open table if successful,
        or NULL if an error occurred.

See Also         tintersect.

Example        /* return a list of all suppliers who have not made
        any orders */

        answer = tdiff(STATIC,
          tproject(TEMP, sups, "s#", NULL),
          tproject(TEMP, orders, "*s#", NULL)
        );



----------------------------------------------------------------
tdrop
----------------------------------------------------------------

Description     Closes and deletes a table.

Declaration      int tdrop(TABLE *t);

Remarks         tdrop closes and subsequently deletes a table and all
        its indexes on disk.  All memory held by the table is
        released.  Because the table is being deleted, any
        unwritten buffers are simply released. If the table
        was in a transaction, it is rolled back, before the
        table is dropped.

        You can also drop temporary tables (TEMP and STATIC.)
        Dropping and closing a temporary table are functionally
        equivelent.

        tdrop drops the table, even if an error occurs.

Return Value     tdrop returns 0 if successful, or an error code if an
        error occurred.

See Also        tclose.

Example        #include <stdio.h>
        #include "crde.h"

        int tbuffers = 128;




                    - 119 -

        int main()
        {
          TABLE *t, *t1;

          /* "Packs" the sups table */

          t = topen("sups");
          if ((t1 = tcopy("dummy")) != NULL) {
            tdrop(t);
            tclose(t1);
            trename("dummy", "sups");
          }
          else
            tclose(t);
        }



----------------------------------------------------------------
tdropindex
----------------------------------------------------------------

Description      Drops a secondary index from a table.

Declaration      int tdropindex(TABLE *t, char *id);

Remarks          tdropindex removes a secondary index from a table.
        td is an index descriptor like the one described in
        tindex.  The index described must exist to be dropped.

        tdropindex works only with secondary indexes--you
        cannot drop a primary index.

Return Value     tdropindex return 0 if successful, or an error code
        if an error occured.

See Also        tindex.

Example        #include "crde.h"

        int main()
        {
          TABLE *t;

          t = topen("sups");
          tdropindex(t, "sname");
          tclose(t);
        }










                    - 120 -

----------------------------------------------------------------
tempty
----------------------------------------------------------------

Description      Removes all rows from a table.

Declaration      int tempty(TABLE *t);

Remarks          tempty removes all rows from the table.

        tempty is superior to tdelete for deleting all rows
        from a table for a couple of reasons.  tempty
        physically reclaims all deleted space from the table.
        In addition, tempty is much faster than tdelete.

Return Value     tempty returns 0 if successful, or an error code if
        an error occured.

See Also         tdelete.



----------------------------------------------------------------
terase
----------------------------------------------------------------

Description      Erases a table.

Declaration      int terase(char *name);

Remarks          terase erases the named table and all associated
        indexes.

        Never erase an open table, use tdrop instead.

Return Value     terase returns 0 if successful or -1 if an error
        occured.

See Also         tdrop.



----------------------------------------------------------------
texportascii
----------------------------------------------------------------

Description      Exports a table to an ascii file.

Declaration      long texportascii(TABLE *t, char *dest);

Remarks         texportascii exports a CRDE table to an ascii file.
        t is the table to be exported.  dest is the filename
        of the ascii file to export to.  dest is automatically
        overwritten by texportascii.




                    - 121 -

        texportascii writes columns to the ascii file in
        the order in which they appear in t.   A comma
        seperates each column in the record, and each record
        appears on an individual line of the ascii file.
        Each column is output in a format according to its
        datatype:

              CRDE
        type    Format of column in ascii file

        c          CRDE will output a character string enclosed
            in quotes "".

        i          CRDE will output a base 10 integer.  Negative
            numbers will be preceded with a negative sign -.

        l          CRDE will output a base 10 integer. Negative
            numbers will be preceded with a negative sign -.

        d          Dates are output in the format mm/dd/yyyy and
            enclosed in quotes.

        f    Floating point numbers are output with 4 decimal
            places.  Negative numbers are preceded with a
            negative sign.

        $          Dollar values are output with 2 decimal places.
            Negative numbers are preceded with a negative
            sign.

Return Value     texportascii returs the number of rows exported if
        successful, or an error code if an error occurred.

See Also        timportascii.

Example:          #include <stdio.h>
        #include "crde.h"

        int tbuffers = 128;

        int main()
        {
          TABLE *t;
          long result;

          t = topen("sups");

          result = texportascii(t,  "sups.txt");
          if (result < 0)
            printf("Export failed (error code %d "
              "occurred.\n", terrno);
          else
            printf("Export successful.  %ld rows "
              "exported.\n", result);

          tclose(t);
        }

                    - 122 -

----------------------------------------------------------------
texportdBASE
----------------------------------------------------------------

Description     Exports a table to dBASE III Plus format.

Desclaration    long texportdBASE(TABLE *t, char *dest);

Remarks        texportdBASE exports a CRDE table to a dBASE III
        Plus file named dest.  texportdBASE does not assume
        the ".DBF" extension so you must add this on your
        own.

        texportdBASE only exports the actual table.  No
        indexes, primary or secondary, are exported.

        texportdBASE translates CRDE columns into equivelent
        dBASE fields.  Column names are converted to
        uppercase and placed in the dBASE header in the same
        order they appear in the CRDE table.  Columns are
        converted into the smallest dBASE type which can
        accurately hold the data.

        CRDE
        type    How CRDE column is converted to dBASE.

        c     Character field with a length equaling the
            length of the c column - 1.

        i     Numeric field with a length of 6 and 0 decimal
            places.


        l    Numeric field with a length of 11 and 0 decimal
            places.

        d      CRDE dates are converted to dBASE format.

        f      Numeric field with a length of 19 and 4 decimal
            places.

        $      Numeric field with a length of 19 and 2 decimal
            places.

Return Value    texportdBASE returns the number of rows exported or an
        error code if an error occurred.

See Also    timportdBASE.










                    - 123 -

----------------------------------------------------------------
tflush
----------------------------------------------------------------

Description      Flushes a table's buffers.

Declaration      int tflush(TABLE *t);

Remarks          tflush flushes any unwritten buffers to disk.

        How tflush responds depends on the particular mode a
        table is in:

        T_NORMAL    tflush will have no effect since CRDE
                automatically flushes all changes after
                every function call in normal mode.

        T_WRITEC    All unwritten buffers are flushed to
                disk.

        T_STATIC         All unwritten buffers are flushed to
                disk.  If the temporary table does not
                yet have an image on disk, tflush will
                force the creation of one automatically.

        T_TEMP        Same as T_STATIC.

Return Value     tflush returns 0 if succesful, of an error code if an
        error occurred.

See Also         twritec, tnormal, trelease.



----------------------------------------------------------------
tget
----------------------------------------------------------------

Description      Reads rows from a table into an array.

Declaration      int tget(TABLE *t, void *array, int n, ...);

Remarks        tget reads up to n rows from t and copies them into
        the buffer pointed to by array.  The buffer should
        be an array of structures similar to the structure
        of t and should be large enough to hold at least n
        rows.

        tget expects a series of arguments which form a search
        expression like the one described in tselect.  Only
        those records which match the search criteria are
        placed in the buffer.  tget stops after the entire
        table has been scanned or n records are retreived,
        whichever occurs first.  The order in which the
        records are retreived is undefined unless there no



                    - 124 -

        search expression and the physical order of the records
        in the table is known (for example, after a tsort
        function call.)

        Note that the rows retreived are only copies of the
        records in the table.  Rows are not removed from the
        table by tget, nor will changing the rows retreived
        in the buffer have any effect on the rows actually in
        t.

Return Value     tget returns the number of rows retreived if successful,
        or an error code if an error occurred.

See Also         tgetif, tselect.

Example:          /* load some suppliers into a buffer */

        sup buf[50];

        sups = topen("sups");
        tget(sups, buf, 50, "city", EQ, "Atlanta", NULL);



----------------------------------------------------------------
tgetif
----------------------------------------------------------------

Description      Reads rows from a table into an array.

Declaration      int tget(TABLE *t, void *array, int n,
          int (*action)(void *), ...);

Remarks          tgetif is exactly like tget except it accepts an
        additional parameter action.  Every row which matches
        the search expression is passed to action for further
        evaluation.  action should behave exactly as the
        action described in tselectif:

              Result of action    What happens

            > 0              Row is retrieved.

                 = 0             Row is not retrieved.

            < 0              An error occurred.  tgetif is
                    aborted and returns the result
                    of action.

              See tselectif for more details on action.

Return Value     tget returns the number of rows retreived if
        successful, or an error code if an error occurred.

See Also         tget, tselect, tselectif.



                    - 125 -

----------------------------------------------------------------
tgroup
----------------------------------------------------------------

Description      Groups a table on one or more columns.

Declaration      TABLE *tgroup(char *name, TABLE *t, char *groupby, char
          *td, int (*summary) (void *in, void *out, int first);

Remarks        tgroup is an extremely powerful function which organzies
        a table into groups and allows you to perform
        calculations upon those groups by writing a special
        grouping function.

        tgroup creates a table name from the source table t.
        groupby is a column list containing the columns to
        group on.  For example, "city" would group on the city
        column, while "city,status" would group on both the
        city and status columns.  Up to 8 columns may be
        specified in groupby.  td is a table descriptor which
        describes the output table.  It is up to the group
        function summary  to generate a table which corresponds
        to td.

        summary is a user-defined function which creates the
        result table.  It accepts 3 parameters in (void *),
        out (void *), and first (int), and must return an int.

        in points to the next record in t being passed to summary.

        out points to the record currently being generated by
        summary.  It has the same structure as described in td.

        first determines the position of the row (pointed to by
        in) within that particular group.  If first is 1 then
        the row is the first in that group, or 0 if it is not.
        After every row in the group has been processed, tgroup
        calls summary once more with first set to -1.  This
        allows summary to do any clean up or overall calculations
        on the group.


        Writing grouping functions.

        A group function should have a declaration similar to

        int groupfunc(void *in, void *out, int first);

        Every row in each group is passed to groupfunc and is
        referenced  by in.  The information in used to calculate
        theoutput row for the group.  out is a pointer to the
        output row for a particular group.  groupfunc is
        responsible for building out.  first is a parameter
        used to determine whether a row is the first or last
        element in the group.  The information is used to
        determine when to initialize the group and whether to


                    - 126 -

        include the group in the answer table.  The following
        describes how tgroup uses groupfunc to generate the
        answer table.

        1. tgroup calls the groupfunc with first == 1.  in
        points to the first row in the group.  The groupfunc
        is expected to initialize out.

        2. tgroup calls groupfunc with first == 0 for every
        other row in the group.  Each row is accessed through
        the pointer in.  groupfunc should update any
        calculated columns in out based   on each row in the
        group.

        3.  After every row in the group has been passed to
        groupfunc, tgroup calls groupfunc once more time
        with first == -1.  groupfunc should perform any final
        calculations (now that all rows in the group have
        been processed), and do any cleanup necessary.  out
        should now contain the values which will appear in
        the answer table.  However, groupfunc can also decide
        whether or not it wants to include out in the answer
        table.  If groupfunc returns > 0 then tgroup will
        include out.  If groupfunc returns 0 then tgroup will
        not include the row.

        tgroup repeats step 1-3 for every group in the table.
        In steps 1. and 2., groupfunc should return a value
        >= 0 if no errors occurred.  Regardless of the value
        of first, if the groupfunc returns a value < 0, tgroup
        aborts and returns NULL.  terrno will be set to the
        value returned by groupfunc.  A well designed groupfunc
        should include error-handling and perform any necessary
        cleanup if an error occurs.

        Complete details on writing group functions and full
        examples are provided in Chapter 12, Performing Queries.

Return Value     tgroup returns a pointer to an open table if successful,
        or NULL if an error occurred.

Example          /* Simply group function to count suppliers in each
        city in the sups table */

        int CountSups(void *in, void *out, int first)
        {
          typedef struct {
            char city[26];
            int count;
          } gs;

          switch (first) {
            case 1:
              strcpy(((gs *)out)->city, ((sup *)in)->city);
              ((gs *)out)->count = 0;



                    - 127 -

            case 0:
              ((gs *)out)->count++;
              break;
          }

          return 1;
        }

        answer = tgroup(STATIC, sups, "city", "city c26, count i",
          CountSups);



----------------------------------------------------------------
timportascii
----------------------------------------------------------------

Description      Imports an ascii file.

Declaration      TABLE *timportascii(char *name, char *td, char *source);

Remarks:        timportascii imports an ascii file into a CRDE table.
        td is a table descriptor which describes the table.
        source if the file name of the ascii file to import.

        timportascii reads columns from the ascii file in the
        order in which they appear in td.  A comma should
        seperate each column in the record, and each record
        should appear on an individual row of the ascii file.
        In addition, each column must conform to the following
        format, dependent upon the datatype of the column
        being imported:

        CRDE
        type    Expected format of column in ascii file

        c     A character string contained in quotes "".
            If the string is too large for the column, it
            will be truncated to fit.


        i          A base 10 integer is expected. Negative values
            should be preceded with a negative sign -.  If
            the integer is out of range for the column,
            the value for that column is undefined.

        l          A base 10 integer is expected.  Negative values
            should be preceded with a negative sign -.  If
            the integer is out of range for the column, the
            value for that column is undefined.

        d          A date in the format of mm/dd/yyyy is expected.
            The date must be enclosed in quotes.  Whitespace
            is ignored.




                    - 128 -

        f          A floating point value with any number of decimal
            places.  Exponential notation may be used, +/-eNN.
            Negative numbers must be preceded with a negative
            sign -.  If the number is out of range, the
            resulting value imported is undefined.

        $          Same as f.

Return Value     timportascii returns a pointer to an open table if
        successful, or NULL if an error occurred.

See Also        texportascii.

Example        #include "crde.h"

        int tbuffers = 128;

        int main()
        {
          TABLE *t;

          /* Program to import the sups table from a pre-
          existing ascii file. */

          t = timportascii("sups", "*s# i, sname c26, city c26, "
            "status i", "sups.dat");

          tclose(t);
        }



----------------------------------------------------------------
timportdBASE
----------------------------------------------------------------

Description    Imports a dBASE III or dBASE III Plus data file.

Declaration    TABLE *timportdBASE(char *name, char *source);

Remarks        timportdBASE imports a dBASE III or dBASE III Plus
        compatible data file.  source  is the name the the
        dBASE file to import.  timportdBASE does not assume
        the ".DBF" extension so you must remember to add it
        yourself.

        timportdBASE only inports dBASE data files.  It cannot
        import dBASE indexes or memo files.  timportdBASE
        determines the CRDE table structure by reading the
        dBASE file header and converting the fields to
        CRDE equivelents.







                    - 129 -

        dBASE
        type    How dBASE field is converted to CRDE

        C          c column equaling length of dBASE field + 1.

        D          dBASE's dates are converted to CRDE's date
            format.

        N         If the number of decimal places equals 2 then
            the colum type is $.  If decimal places is > 0
            then column is type f.  If the length of the
            number field is > 11 then the column is type f.
            If the length of the number field is < 7 then
            the column is type i.  Otherwise the column is
            type l.

        L          Logical fields are converted to type i. If the
            field is 'Y' or 'T' the column will have a value
            of 1, otherwise 0.


        M          CRDE does import memo fields.  Any memo fields
            in the dBASE file are ignored.

Return Value    timportdBASE returns a pointer to an open table if
        successful, or NULL if an error occurs.

See Also    texportdBASE.

Example        #include <stdio.h>
        #include "crde.h"

        int tbuffers = 128;

        int main()
        {
          TABLE *t;

          t = timportdBASE("Cust", "Cust.dbf");
          if (t) {
            printf("dBASE import successful.  %ld rows"
              " imported.\n");
            tclose(t);
          }
          else
            printf("Import failed (error code = %d).\n", terrno);
        }











                    - 130 -

----------------------------------------------------------------
tindex
----------------------------------------------------------------

Description:       Creates a secondary index.

Declaration:      int tindex(TABLE *t, char *id);

Remarks:         tindex creates a secondary index on a table specified
        by the index descriptor id.  id is an asciiz (null-
        terminated) string of column names seperated by
        whitespace.  Whitespace consists of all characters
        classified by the isspace() function plus the comma
        ','.  id cannot contain more than 8 columns, nor can
        a column occur more than once in the descriptor.


        Descending keys.

        You can mark any of the columns to be descending keys
        by prepending the column name with an exclaimation
        point '!'.  Normally, the only time you would use
        descending keys is in describing the sort order in a
        tsort function.  Although it is completely legal to
        specify descending keys in normal indexes, it is
        rarely useful to do so.  Using either ascending or
        descending keys in an index has no effect on
        performance. CRDE handles both kinds of keys
        transparently.

        Once the secondary index is created, it is
        automatically maintained by CRDE. It is opened and
        closed whenever along with the table and updated
        whenever records are inserted,changed, or deleted.
        Unlike the primary index, a secondary index may contain
        duplicates, so it cannot be used to prevent duplicate
        keys in the table (only the primary index can do that.)
        Secondary indexes are mostly used for speeding up
        various CRDE operations.  Since CRDE knows all about
        your indexes, it can use them to help in searches,
        sorting, joining, etc. to significantly improve
        performance.

        Calling tindex on an index which already exist rebuilds
        that particular index.  One drawback is that tindex
        only works with secondary indexes, you can't rebuild
        the primary index with tindex.

Return Value    tindex returns 0 if successful, or an error code if an
        error occurred.

See Also       tdropindex.






                    - 131 -

Example        #include "crde.h"

        int tbuffers = 128;

        int main()
        {
          TABLE *t;

          /* create index on "sname" on Sups table. */

          t = topen("sups");
          tindex(t, "sname");
          tclose(t);
        }



----------------------------------------------------------------
tindexes
----------------------------------------------------------------

Description      Calculates the number of secondary indexes.

Declaration      int tindexes(TABLE *t);

Remarks        tindexes returns the number of secondary indexes on a
        table.  The primary index is not counted.

Return Value     tindexes returns the number of secondary indexes on a
        table, or an error code if an error occurred.

See Also         tkeyed.



----------------------------------------------------------------
tinsert
----------------------------------------------------------------

Description      Insert record(s) into a table.

Declaration      int tinsert(TABLE *t, void *recs, int n);

Remarks        tinsert inserts n records into a table. The records
        are contained in the structure, or array of structures,
        pointed to by recs.  The structure pointed to by recs
        should exactly  mimic the structure of the table.  n
        should not be larger than the number of rows in the
        array.

        If t is keyed, then only those records which do not
        violate the primary key will be inserted.

Return Value     tinsert returns the number of rows inserted if
        successful or an error code if an error occurred.



                    - 132 -

See Also         tload, treplace.

Example          #include <stdio.h>
              #include "crde.h"

        int tbuffers = 128;

              typedef struct {
          char last[16], first[11], phone[9];
        } person;

              int main()
              {
          TABLE *t;
          person p;
          char c[10];

          /* create a simple phone number list */
          t = tcreat("phone", "last c16, first c11, phone c9");

          twritec(t);
          do {
            printf("\nlast name:  ");
            gets(p.last);
            printf("first name: ");
            gets(p.first);
            printf("phone no:   ");
            gets(p.phone);

            tinsert(t, &p, 1);

            printf("Enter another phone number (Y/N)?");
            gets(c);
          } while (toupper(*c) == 'Y');

          tclose(t);
        }



----------------------------------------------------------------
tintersect
----------------------------------------------------------------

Description      Returns the intersection of two tables.

Declaration      TABLE *tintersect(char *name, TABLE *t1, TABLE *t2);

Remarks          tintersect returns the relational intersection of
        tables t1 and t2. The result table will have a
        structure identical to t1 and contain all the
        records found in both t1 and t2.






                    - 133 -

        tintersect requires that t1 and t2 be compatible,
        i.e. that they have equivelent types and sizes on a
        column by column basis.  However they may have
        different primary or secondary keys.

        tintersect works differently depending upon whether
        t1 is keyed or not.  If t1 is keyed then tintersect
        will return every record in t1 whos key values matches
        the key values of any record in t2.  If t1 is not keyed,
        then only those records which entirely match a record
        in t2 will be included.

        If t1 or t2 is a TEMP table, then tintersect will drop
        either or both of them, respectively.  Otherwise, t1
        and t2 are unaffected by tintersect.

Return Value     tintersect returns a pointer to an open table if
        successful, or NULL if an error occurred.

See Also         tdiff, tunion.

Example        /* query which uses tintersect to find those parts
        ordered by both supplier #1 and supplier #2 */

        TABLE *answer;

        answer = tintersect(STATIC,
          tproject(TEMP, orders, "*p#", "s#", EQ, 1, NULL),
          tproject(TEMP, orders, "*p#", "s#", EQ, 2, NULL)
        );



----------------------------------------------------------------
tjoin
----------------------------------------------------------------

Description    Performs the relational join of two tables.

Declaration      TABLE *tjoin(char *name, TABLE *t1, char *cl1, TABLE
          *t2, char *cl2, char *pl);

Remarks          tjoin joins tables t1 and t2 creating a new table.
        The ability to join tables, relate information between
        tables, is at the heart of relational philosophy.
        Tables are related by "join" columns, tjoin works by
        connecting all the rows in t1 whose join columns have
        the same values as the corresponding join columns in t2.

        cl1 and cl2 are column lists describing the join
        columns for tables t1 and t2 respectively.  cl1 and
        cl2 must contain the same number of columns, and each
        must be of identical types on a column by column basis.
        Up to 256 join columns may be specified for each table.




                    - 134 -

        pl describes the result table.  pl is special column
        list which may include a primary key by prepending any
        columns with an asterisk '*'.  In addition, pl may
        contain columns from either t1 or t2.  However, if t1
        and t2 both contain a column of the same name, pl can
        only include one of them, since by definition every
        column name in a table must be unique.  By default,
        tjoin will chose the column in t1 over t2.  You should
        keep this in mind when designing tables that you may
        plan on joining later.

        If either t1 or t2 are TEMP tables, tjoin will drop
        either or both of them, respectively.  Otherwise, t1
        and t2 are unaffected by tjoin.

Return Value     tjoin returns a pointer to an open table if successful,
        or NULL if an error occurred.

Example        /* simple 2-table join containing all orders made by
        supplier #100 */

        TABLE *answer;

        answer = tjoin(STATIC,
          tselect(TEMP, sups, "s#", EQ, 100, NULL), "s#",
          orders, "s#",
          "s#,sname,city,status,p#,qty"
        );



----------------------------------------------------------------
tkeyed
----------------------------------------------------------------

Description      Determines whether a table is keyed or not.

Declaration      int tkeyed(TABLE *t);

Remarks         tkeyed returns 1 if t has a primary key, or 0 if not.
        tkeyed(t) + tindexes(t) returns the total number of
        indexes on a table.

Return Value    tkeyed return 1 if t is keyed or 0 if not. An error
        code is returned if an error occurred.

See Also         tindexes.

Example        #include <stdio.h>
        #include "crde.h"

        int tbuffers = 128;






                    - 135 -

        int main()
        {
          TABLE *t;

          t = topen("sups");
          if (tkeyed(t))
            printf("Sups table is keyed.\n");
          else
            printf("Sups table is not keyed.\n");
          tclose(t);
        }



----------------------------------------------------------------
tkeys
----------------------------------------------------------------

Description      Describes the secondary indexes on a table.

Declaration      TABLE *tkeys(char *name, TABLE *t);

Remarks        tkeys is a complementary function to tstruct.  It
        creates a table describing a table's secondary keys.

        tkeys creates a table equivelent to

            tcreat(name, "ext c4, key c144")

        and fills it with information about a table's
        secondary keys.  The ext column contains the filename
        extension of the index being described and is of the
        format "s[n]" where [n] is a digit from 0 to 7.  The
        key column contains the index descriptor which created
        the index.

        If t is a TEMP table, then it will be dropped
        automatically by tkeys.  Otherwise, t is unaffected by
        tkeys.

Return Value     tkeys returns a pointer to an open table if successful,
        or NULL if an error occurred.

See Also         tstruct.

Example:          #include <stdio.h>
              #include "crde.h"

        int tbuffers = 128;

              int main()
              {
          TABLE *t;

          /* Program to display information on all the
          secondary indexes on Sups */


                    - 136 -

          t = topen("sups");

          tview(tkeys(TEMP, t), NULL, NULL, 1, 1, 80, 25,
          0x07, 0x07, 0x07, NULL);

          tclose(t);
        }



----------------------------------------------------------------
tload
----------------------------------------------------------------

Description      Loads a record into a table column by column.

Declaration      int tload(TABLE *t, ...);

Remarks          tload expects a series of arguments, one for each
        column of the table.  Each argument must have the
        exact same type as the corresponding column in t.
        In the case of a char column, tload expects a char *
        to string which may be of any length.  tload will
        automatically truncate the string to fit in the
        appropriate column.  The resulting row is inserted
        into t.

        tload is useful when filling tables directly by the
        program without user input.

Return Value     tload returns 0 on success, or an error code if an
        error occurred.

See Also        tinsert, treplace.

Example          #include <stdio.h>
              #include "crde.h"

        int tbuffers = 128;

             int main()
        {
          TABLE *t;

          t = tcreat("customer", "*cust# , name c26, city c26,"
            " state c3, zip c6, balance $");

          tload(t, 1,             /* cust# */
            "Star Software",      /* name  */
            "Anywhere",           /* city  */
            "MI",                    /* state */
            "48262",                  /* zip   */
            0.0);                     /* balance */





                    - 137 -

          /* load some more records... */
          tload(t, 2, "Kent's Warehouse", "Jonesville", "NY",
            10079, 0.0);
          tload(t, 3, "The Grocery Store", "Auburn", "GA",
            72623, 0.0);

          tclose(t);
        }



----------------------------------------------------------------
tlookup
----------------------------------------------------------------

Description    Lookup rows in a table.

Declaration      int tlookup(TABLE *t, ...);

Remarks         tlookup is used to lookup values in a table.
        tlookup expects a series of arguments which form a
        search expression like the one described in tselect.
        tlookup will return 1 if it finds a record matching
        the criteria, or 0 if it does not.

        tlookup is useful for supporting lookup tables to
        validate entries into a database.  For example you
        might enter a customer number into an order table,
        then call tlookup to verify that that customer number
        exists in the customer table.

        tlookup is very similar to tscan.  The primary
        difference between the two is that tlookup stops
        after it finds the first record matching the criteria,
        while tscan attempts to find all records matching the
        criteria.  Subsequently, tlookup will generally be
        much faster than tscan.

        If t is a temporary table then it is automatically
        dropped by tlookup, even if an error occurs.  Otherwise,
        t is unaffected by tlookup.

Return Value     tlookup returns 1 if a matching record is found or 0
        if not.  It returns an error code if an error occurred.

See Also         tlookupif, tselect.

Example         #include <stdio.h>
        #include "crde.h"

        /* function to add an order to the order table - uses
        table lookup to validate the order */

              int AddOrder(order *o)
             {
          int i;


                    - 138 -

          /* validate new order against customer lookup table
          to insure o->custno is a valid customer.  Returns 1
          if successful. */

          i = tlookup(cust, "cust#", EQ, o->custno, NULL);
          if (i > 0)
            i = tinsert(orders, o, 1);
          return i;
        }



----------------------------------------------------------------
tlookupif
----------------------------------------------------------------

Description      Lookup rows in a table.

Declaration      int tlookupif(TABLE *t, int (*action)(void *), ...);

Remarks          tlookupif is exactly like tlookup except it accepts
        an additional parameter action.  Every row which
        matches the search expression is passed to action
        for further evaluation.  action should behave exactly
        as the action described in tselectif:

        Result of action    What happens

            > 0              Row found.  tlookupif returns 1.

            = 0              Row not found.  tlookupif keeps
                    looking.

            < 0             An error occurred. tlookupif
                    aborts and returns the result
                    of action.

              See tselectif for more details on action.

        If t is a temporary table then it is automatically
        dropped by tlookupif, even if an error occurs.
        Otherwise, t is unaffected by tlookupif.

Return Value     tlookupif returns 1 if a matching record is found or 0
        if not.  It returns an error code if an error occurred.

See Also         tlookup, tselect, tselectif.

Example        /* lookup function to determine if there are any red
        or blue parts */

        int RedOrBlue(void *rec)
        {
          return stricmp(((part *)rec)->color, "red") == 0 &&
            stricmp(((part *)rec)->color, "blue") == 0;
        }


                    - 139 -

        if (tlookupif(parts, RedOrBlue, NULL) > 0) {
          /* ... */
        }



----------------------------------------------------------------
tmark
----------------------------------------------------------------

Description      Determines whether a table is in a transaction.

Declaration      int tmark(TABLE *t);

Remarks          If t is in a transaction, tmark returns a value > 0.
        If t is not in a transaction, tmark returns 0.

Return Value     tmark returns > 0 if a transaction on the table is
        pending, 0 otherwise. An error code if returned if t
        is not a table.

See Also         tcommit, trollback, ttransact.

Example        #include <stdio.h>
        #include "crde.h"

        int tbuffers = 128;

        int VerifyTrans(TABLE *t)
        {
          if (tmark(t))
            printf("Table is in a transaction.\n");
          else
            printf("Table is not in a transaction.\n");
        }

        int main()
        {
          TABLE *t;

          t = topen("sups");

          VerifyTrans(t);
          ttransact(t);
          VerifyTrans(t);
          trollback(t);
          VerifyTrans(t);

          tclose(t);
        }








                    - 140 -

----------------------------------------------------------------
tmax
----------------------------------------------------------------

Description     Calculates the maximum value in a column.

Declaration     int tmax(TABLE *t, char *c, void *result);

Remarks          tmax calculates the maximum value in column c and
        places the result in result.  tmax works with all
        column types:  c, i, l, d, f, $.  The result type
        is identical to the type of the named column.

        result is undefined if an error occurs.

        If t is a TEMP table then it is automatically dropped
        by tmax, even if an error occurs.  Otherwise t is
        unaffected by tmax.

Return Value      tmin returns 0 if successful or an error code if an
        error occurred.

See Also    taverage, tcount, tmin, tsum.

Example        /* query to find all suppliers who have a status >=
        the supplier with the highest status in Atlanta */

        double d;

        tmax(tselect(TEMP, sups, "city", EQ, "Atlanta", NULL),
          "status", &d);

        answer = tselect(STATIC, sups, "status", GE, (int)d, NULL);



----------------------------------------------------------------
tmin
----------------------------------------------------------------

Description      Calculates the minimum value in a column.

Declaration      int tmin(TABLE *t, char *c, void *result);

Remarks          tmin calculates the minimum value in column c and
        places the result in result.  tmin works with all
        column types:  c, i, l, d, f, $.  The result type
        is identical to the type of the named column.

        result is undefined if an error occurs.

        If t is a TEMP table then it is automatically dropped
        by tmin, even if an error occurs.  Otherwise, t is
        unaffected by tmin.




                    - 141 -

Return Value     tmin returns 0 if successful or an error code if an
        error occurred.

See Also    taverage, tcount, tmax, tsum.

Example        /* query to find all suppliers who have a status <=
        the supplier with the lowest status in Atlanta */

        double d;

        tmin(tselect(TEMP, sups, "city", EQ, "Atlanta", NULL),
          "status", &d);

        answer = tselect(STATIC, sups, "status", LE, (int)d, NULL);



----------------------------------------------------------------
tmode
----------------------------------------------------------------

Description      Determines table mode.

Declaration     int tmode(TABLE *t);

Remarks     tmode returns 1 of 4 constants describing the current
        mode of the table:

        T_NORMAL    The table is in normal mode.

        T_WRITEC     The table is in write-caching mode.

        T_TEMP         The table is a TEMP temporary table.

        T_STATIC       The table is a STATIC temporary table.

        A permanent table will return either T_NORMAL or
        T_WRITEC.  A temporary table will return either T_TEMP
        or T_STATIC.

Return Value    tmode returns one of the constants above, or an error
        code if an error occurred.

See Also         tnormal, twritec.

Example        /* routine to display a table's current mode */

        int PrintMode(TABLE *t)
        {
          printf("Table mode = ");
          switch (tmode(t))
          {
            case T_NORMAL :
              printf("NORMAL");
              break;



                    - 142 -

            case T_WRITEC :
              printf("WRITEC");
              break;
            case T_STATIC :
              printf("STATIC");
              break;
            case T_TEMP :
              printf("TEMP");
              break;
          }
          printf(".\n");
        }



----------------------------------------------------------------
tnormal
----------------------------------------------------------------

Description      Puts a table in normal mode.

Declaration     int tnormal(TABLE *t);

Remarks        tnormal is turns off the write-caching mode set by
        twritec.  Any changes will be immediately written to
        disk after every CRDE function call.   Although this
        greatly improves data integrity, it can have dramatic
        effects upon performance.  twritec explains in more
        detail how CRDE buffering works and when you should
        switch between normal and writec modes.

        By default, all tables begin in normal mode (except
        temporary tables).

        tnormal has no effect on temporary tables (both TEMP
        and STATIC), tables in a transaction, or tables already
        in normal mode.

Return Value     tnormal returns 0 if successful, or an error code if
        an error occurred.

See Also        twritec.

Example        #include <stdio.h>
        #include "crde.h"

        typedef struct {
          int code;
          char des[64];
        } error_t;

        TABLE *err;

        void DisplayErr(void)
        {
          error_t e;


                    - 143 -

          if (terrno < 0) {
            if (tget(err, &e, 1, "code", EQ, terrno) > 0) {
              printf("Error %d: %s.\n", e.code, e.des);
            }
            else {
              printf("Error table fault.\n");
              exit(1);
            }
          }
        }


        void LoadErr(void)
        {
          /* create table containing error codes and messages */

          err = tcreat("error", "*code i, des c64");
          twritec(err);
          tload(err, -11, "Out of memory");
          tload(err, -16, "Integrity check");
          tload(err, -21, "Disk seek");
          /* load rest of error codes here... */

          tnormal(err);
        }


        int tbuffers = 128;

        int main()
        {
          TABLE *t;

          LoadErr();

          t = topen("sups");
          DisplayErr();

          tview(t, NULL, NULL, 3, 3, 78, 23, 0x07, 0x07, 0x07,
            NULL);
          DisplayErr();

          tall(tclose);
        }



----------------------------------------------------------------
topen
----------------------------------------------------------------

Description      Opens a table.

Declaration     TABLE *topen(char *name);




                    - 144 -

Remarks          topen opens the table specified by name and all
        associated indexes.  The table must already exist
        to be opened.

        topen does a number of integrity checks to insure
        that the table and/or any indexes are not corrupt
        topen will automatically attempt to repair any
        corrupted indexes on the table.  However, if the
        table itself is corrupt, you will have to use trepair
        to fix the table.

        topen will fail if it fails to open any of the files
        associated with the table.

Return Value     topen returns a pointer to an open table is successful,
        or NULL if an error occured.

See also         tclose.

Example          #include <stdio.h>
              #include "crde.h"

        int tbuffers = 128;

              int main()
              {
          TABLE *t;

          if ((t = topen("customer")) == NULL) {
            printf("unable to open table.\n");
            exit(1);
          }
          MainProgram();
        }



----------------------------------------------------------------
tproject
----------------------------------------------------------------

Description      Creates a relational projection of a table.

Declaration      TABLE *tproject(char *name, TABLE *t, char  *pl, ...);

Remarks          tproject creates a projection of t.  In relational
        terms, a projection of a table is a table containing
        one or more of the columns of the original.  pl is a
        column list which defines the projected table.  All
        the columns in pl must exist in t, although the
        reverse is not necessarily true.  In addition, the
        columns may be arranged in any order desired.






                    - 145 -

        You may also define a primary key on the projected
        table, which may be different from the original.  You
        can use this technique to extract distinct values in
        a column from a table.  For example, suppose you
        wanted a list of all distinct customer numbers in an
        orders table.  You  might use the following query:

              orders = topen("orders");
              answer = tproject(TEMP, orders, "*cust#", NULL);

        Adding the '*' in front of "cust#" removes any duplicate
        customer numbers from the answer table.

        tproject expects a series of arguments which form a
        search expression like the one described in tselect.
        With this you can project selected subsets of a table,
        or the whole table itself.

        If t is a temporary table then it is automatically
        dropped by tproject, even if an error occurs.  Otherwise,
        t is unaffected by tproject.

Return Value    tproject returns a pointer to an open table if
        successful, or NULL if an error occurred.

See Also         tprojectif, tselect.

Example        #include "crde.h"

        int main()
        {
          TABLE *t, *answer;

          /* Program to display all cities where there are
          suppliers, in alphabetical order */

          t = topen(sups);

          answer = tsort(STATIC,
            tproject(TEMP, sups, "*city", NULL),
            "city"
          );

          tview(answer, NULL, NULL, 3, 3, 78, 23, 0x07, 0x07,
            0x07, NULL);

          tall(tclose);
        }










                    - 146 -

----------------------------------------------------------------
tprojectif
----------------------------------------------------------------

Description      Creates a projection of a table.

Declaration      TABLE *tprojectif(char *name, TABLE *t,
          int (*action)(void *), ...);

Remarks          tprojectif is exactly like tproject except it accepts
        an additional parameter action.  Every row  which
        matches the search expression is passed to action for
        further evaluation.  action should behave exactly as
        the action described in tselectif:

              Result of action    What happens

                 > 0              Row is projected.

                 = 0              Row is not projected.

            < 0              An error occurred.  tprojectif
                    is aborted and returns NULL.
                    terrno is set to the result of
                    action.

        See tselectif for more details on action.

        If t is a TEMP table then it is automatically dropped
        by tprojectif, even if an error occurs.  Otherwise, t
        is unaffected by tprojectif.

Return Value     tprojectif returns a pointer to an open table if
        successful, or NULL if an error occurred.

See Also         tproject, tselect, tselectif.

Example        /* query to list all parts which are red or blue */

        int RedOrBlue(void *rec)
        {
          return stricmp(((part *)rec)->color, "red") == 0 &&
            stricmp(((part *)rec)->color, "blue") == 0;
        }

        answer = tprojectif(STATIC, parts, "*pname", RedOrBlue,
          NULL);











                    - 147 -

----------------------------------------------------------------
trelease
----------------------------------------------------------------

Description     Release memory buffers.

Declaration     int trelease(TABLE *t);

Remarks        trelease releases any memory buffers held by the table.
        Buffers which have not been written to disk (because of
        twritec or ttransact) are not released.  To release all
        memory allocated to a table:  end any transaction, flush
        the table and then call trelease.

        Since CRDE automatically  manages memory between all
        tables, it is never necessary to explicitly release a
        table's memory to help speed another operation.
        However, you can do so manually if you choose with
        trelease.

        tclose and tdrop both automatically release all memory
        allocated to the table as part of their  normal routine.

Return Value     trelease returns 0 if successful or an error code if an
        error occurred.

See Also         tflush, tnormal, twritec.



----------------------------------------------------------------
trename
----------------------------------------------------------------

Description      Renames a table.

Declaration      int trename(char *oldname, char *newname);

Remarks          trename renames a table and all associated indexes.
        A table should be closed before it is renamed.
        trename will fail if a table with the name new
        already exists.

Return Value     trename returns 0 if successful, or -1 if an error
        occured.

See Also         terase.

Example        #include "crde.h"

        int main()
        {
          /* renames "Sups" to "Supplier" */
          trename("sups", "supplier");
        }



                    - 148 -

----------------------------------------------------------------
trepair
----------------------------------------------------------------

Description     Repairs a corrupted table.

Declaration      TABLE *trepair(char *name, char *td);

Remarks         trepair attempts to a repair a corrupted table by
        using the table descriptor td as a model of what the
        table should look like.  It then trys to rebuild the
        table using the infomation provided.

        trepair makes all its assuptions based upon td.  The
        only assumption it makes about the table is that it
        is corrupted.  Thus, it is very important that td is
        correct. If it isn't, trepair could do more harm than
        good, most likely making the table totally unrecoverable.

        trepair can fix logically corrupted tables,
        specifically tables with corrupted headers, usually
        without a hitch.  trepair will attept to recover as
        many rows from the atble as it can, but there is no
        guarentee that all of them will be salvaged.  trepair
        can also fix certain types of physically damaged tables
        as well.  However, trepair is limited to the extent in
        which it can fix physically damaged tables.  It cannot
        undelete tables, nor can it repair lost clusters or
        sectors.  Also, trepair must be able to open the file
        to repair it.

        trepair repairs only the table and primary index, if
        one exists.  Any secondary indexes must be rebuilt
        manually. Simply call tindex to rebuild any secondary
        indexes.

Return Value     trepair returns a pointer to an open table if
        successful, or NULL if an error occurred.

Example        #include <stdio.h>
        #include "crde.h"

        int tbuffers = 128;

        int main()
        {
          TABLE *t;

          t = topen("sups");
          if (!t) {
            printf("Unable to open table, attempting to "
              "repair...\n");
            t = trepair("sups", "*s# i, sname c26, city c26, "
              "status i");




                    - 149 -

            if (!t) {
              printf("Repair failed.\n");
              exit(1);
            }
            else
              printf("Repair successful.  %ld rows "
            "recovered.\n", trowsfound);
          }

          MainProgram();
        }



----------------------------------------------------------------
treplace
----------------------------------------------------------------

Description      Replace a row in a table.

Declaration      long treplace(TABLE *t, void *recs, long n);

Remarks          treplace is identical to tinsert except that if a
        row with the same primary key already exists in the
        table, it is replaced by the new row.

Return Value     treplace returns the number of rows replaced if
        successful, or an error code if an error occurred.

See Also         tinsert.

Example        #include "crde.h"

        typedef struct {
          int s;
          char sname[26];
          char city[26];
          int status;
        } sup;

        sup buf[100];

        int tbuffers = 128;

        int main()
        {
          TABLE *t;
          int i, j;

          t = topen("sups");
          i = tget(t, sups, 100, "city", EQ, "Atlanta", NULL);
          DoOperationsOnBuf();
          treplace(t, sups, i);
          tclose(t);
        }



                    - 150 -

----------------------------------------------------------------
trestruct
----------------------------------------------------------------

Description      Restructures a table.

Declaration     TABLE *trestruct(char *name, TABLE *t, char *td);

Remarks     trestruct creates a restructured version of t.
        trestruct is similar to tproject in that you can
        remove columns, rearrange columns and redefine the
        primary key.  In addition you can also add new columns
        and change the types of columns within a table.

        td is a table descriptor like the one described in
        tcreat.  If the column listed in td is found in t
        then one of two things happens:  if the new column
        is of the same type then data from the original column
        is simply copied to the new column.  If the types are
        different, trestruct tries to convert the data to
        the new type.  The table listing valid type conversions
        is listed below:

              Type conversion table

                c       i        l       d       f      $

        c      x       x       x       x    x       x

        i      x       x       x       -       x       x

        l      x       x       x       -       x       x

        d      x       -       -       x       -       -

        f      x       x       x       -       x       x

        $      x       x       x       -       x       x


              [x] can be converted      [-] cannot be converted

        If a column listed in td not found in t all the values in
        that column are initalized to some inital value (usually
        0 or "").

        Consider the following example:

        t = tcreat("dummy", "*id i, date d, quant i, amount f");

        r = trestruct("restruct", t,
          "*id c6, "    /* converted id from int to char */
          "quant i,"    /* switched date and... */
          "date d, "    /* ...quant columns     */
          "salary $");    /* new column--will be set to 0.00*/



                    - 151 -

        If t is a TEMP table then it is automatically dropped
        by trestruct, even if an error occurs.  Otherwise, t
        is unaffected by trestruct.

Return Value     trestruct returns a pointer to an open table if
        successful, or NULL if an error occurred.



----------------------------------------------------------------
trollback
----------------------------------------------------------------

Description      Rollback a transaction.

Declaration      int trollback(TABLE *t);

Remarks          trollback rolls back a transaction on a table
        returning the table to the state it was in just prior
        to beginning the transaction.

        trollback rolls back the table even if an error occurs.
        In either case, the transaction is considered over.

        Calling trollback outside a transaction has no effect
        on a table.

Return Value     trollback returns 0 if successful or an error code if
        an error occurred.

See Also         tcommit, ttransact.

Example        See tcommit.



----------------------------------------------------------------
trows
----------------------------------------------------------------

Description      Calculates number of rows in a table.

Declaration      long trows(TABLE *t);

Remarks        trows returns a number between 0 and 2147483647.

Return Value     trows returns the number of rows in the table or an
        error code.

See Also         tcols.

Example        /* generic routine to display a table with tview;
        table cannot be TEMP */





                    - 152 -

        int ViewTable(TABLE *t)
        {
          gotoxy(3, 2);
          printf("%ld rows found.", trows(t));
          return tview(t, NULL, NULL, 3, 3, 78, 23, 0x07, 0x07,
            0x07, NULL);
        }



----------------------------------------------------------------
trowsize
----------------------------------------------------------------

Description     Calculates size of a row/record in a table.

Declaration      int trowsize(TABLE *t);

Remarks          trowsize returns a value from 1 to 4000.

Return Value     trowsize returns the size of a single row if
        successful, or an error code if an error occurred.

See Also         tcolsize.

Example        #include <stdio.h>
        #include "crde.h"

        int tbuffers = 128;

        int main()
        {
          TABLE *t;

          t = topen("sups");

          printf("Sups table row = %d bytes in size.\n",
            trowsize(t));

          tclose(t);
        }



----------------------------------------------------------------
tscan
----------------------------------------------------------------

Description      Scans a table.

Declaration      long tscan(TABLE *t, ...);

Remarks        tscan expects a series of arguments which form a search
        expression like the one described in tselect.




                    - 153 -

        tscan doesn't really do much except count the number
        of rows which match the search expression. Generally,
        tscan has little use in most applications.

Return Value     tscan returns the number of rows scanned, or an error
        code if an error occurred.

See Also         tscanif, tselect.

Example        #include <stdio.h>
        #include "crde.h"

        int tbuffers = 128;

        int main()
        {
          TABLE *t;
          long result;

          t = topen("sups");
          result = tscan(t, "city", EQ, "Atlanta", NULL);
          printf("There are %ld suppliers in Atlanta.\n", result);
          tclose(t);
        }



----------------------------------------------------------------
tscanif
----------------------------------------------------------------

Description      Scans a table.

Declaration    long tscanif(TABLE *t, int (*action)(void *), ...);

Remarks          tscanif is exactly like tscan except it accepts an
        additional parameter action.  Every row which matches
        the search expression is passed to action for further
        evaluation.  action should behave exactly as the
        action described in tselectif.    However, tscanif, by
        definition, doesn't really do anything in itself.  It
        simply goes through all the rows matching the search
        expression and passes them to action.  Thus, in this
        case, it doesn't really matter whether action returns
        0 or > 0.

        Result of action    What happens

            > 0              Scanning continues.

            = 0              Scanning continues.

            < 0              An error occurred.  tscanif
                    is aborted and returns NULL.
                    terrno is set to the result of
                    action.


                    - 154 -

        See tselectif for more details on action.

        tscanif can be used for reporting.  Since action
        receives a copy of every record scanned, simply design
        an action function to print out the record.

Return Value     tscanif returns the number of records scanned, or an
        error code if an error occurred.

See Also         tscan, tselect, tselectif.

Example        See Chapter 12, Creating Reports.



----------------------------------------------------------------
tselect
----------------------------------------------------------------

Description      Selects rows/records from a table.

Declaration      TABLE *tselect(char *name, TABLE *t, ...);

Remarks          tselect creates a table, identical in structure t,
        containing records from t matching a specified search
        criteria.

        tselect expects a NULL terminated list of arguments
        which formulate the search expression.  A search
        expression contains a number of conditions.  Only
        those records which match all the conditions in the
        search expression are selected.

        A typical tselect function call will have the following
        format:

        TABLE *tselect(name, t, colname1, relop1, value1,
          colname2, relop2, value2, etc..., NULL);

        Each condition is a set of exactly three arguments:
        colname[n], relop[n], and value[n], described below...

        colname[n] is any valid column name as an asciiz
        string (char *).

        relop[n] is a relational operator (int) defined in
        crde.h.  Valid relops are:

        relop        C Equivelent

        EQ         ==

        LT                <

        GT                >



                    - 155 -

        LE,LTE            <=

        GE,GTE            >=

        NE,NEQ            !=

        value[n] is some value, either a constant or a
        variable.  The type of value[n] must be exactly the
        same as the column type of colname[n].  In the case
        of a char type, value[n] may be a char * pointing to
        a string of any length.

        An expression may contain no conditions, in whichcase,
        every record is selected.  Otherwise a row must match
        every condition to be selected.

        Here are some examples:

                  /* selects every record in table */
        tselect(TEMP, t, NULL);

                  /* selects every record where cust# == 131 */
        tselect(TEMP, t, "cust#", EQ, 131, NULL);

        /* selects every record where cust# == 131 && age > 21 */
        tselect(TEMP, t, "cust#", EQ, 131, "age", GT, 21, NULL);

        /* "qty" in this case is of type long - note hold the
        value must be cast as long */
        tselect(TEMP, t, "qty", GT, 1000L, NULL);

        /* select which uses variables */
        int drinkage = 21;
        ...
        tselect(TEMP, t, "name", EQ, "Mark", "age", GTE, drinkage,
          NULL);

        A search expression may not contain more than 16
        conditions.

        CRDE uses a powerful search engine to analyze and
        perform searches. The search engine knows about a
        table's columns and indexes and will automatically
        determine the fastest way to perform the search.  The
        search engine will use any indexes that are available,
        but can search the table sequentially as well.  In
        some cases it may opt for a sequential search, even
        if an available index exists, if it thinks it can
        perform the search faster that way.

        It also makes no difference in what order an expression
        is organized, the search engine always reorganizes an
        expression to produce the fastest possible search.





                    - 156 -

        Several other functions besides tselect utilize the
        search engine to perform conditional operations on a
        table.  These "search related functions" (excluding
        tselect) are:  tproject, tdelete, tchange, tscan,
        tlookup, and tget, respectively.  In addition, each
        search related function has a cousin function known as
        "..if functions", which provide even more powerful
        search capabilities including user-defined searches,
        nested queries, correlated sub-queries, and more.
        These are tselectif, tprojectif, tdeleteif, tchangeif,
        tscanif, tlookupif, and tgetif.  For more on the
        capabilities of "..if functions" see tselectif.

        If t is a TEMP table then it is dropped automatically
        by tselect, even if an error occurs.  Otherwise, t is
        unaffected by tselect.

Return Value    tselect returns a pointer to an open table if
        successful, of NULL if an error occurred.

See Also         tselectif, tproject, tdelete, tchange, tscan, tlookup,
        tget.



----------------------------------------------------------------
tselectif
----------------------------------------------------------------

Description      Selects rows/records from a table.

Declaration      TABLE *tselectif(char *name, TABLE *t,
          int (*action)(void *), ...);

Remarks          tselectif is one of seven "..if functions" which
        are identical to their regular counterparts in every
        respect except one: each accepts an additional
        parameter action.

        action is a user defined function which futher
        evaulates the row.  Every row which passes the initial
        search criteria is passed to action for further
        evaluation.

        action must return type int.  The result of action
        determines how the record should be processed:

              Result of action    Meaning

             > 0              Select the row.

                 = 0              Do not select the row.






                    - 157 -

            < 0              An error occurred.  tselectif
                    aborts and returns NULL.
                    terrno is set to the result of
                    action.

        action may modify the record passed to it without harm
        (although this does have side effects when used in
        tchangeif).  action can perform queries on other
        tables based on the information passed to it.  action
        can even perform queries on t, allowing for extremely
        powerful and complex querying capabilities.

        However, there is one restriction.  action is
        disallowed from altering t in any way.  When any
        "..if function" calls action, t is automatically placed
        in a special read-only mode.  Because of the complex
        nature in which CRDE performs searches, t cannot be
        changed during the course of a query.  CRDE functions
        which attempt to alter t will return a "table may not
        be modified" error.

        CRDE functions which can change a table are listed below:

        tadd          tchange        tchangeif     tchcol
        tdelete        tdeleteif     tdropindex     tempty
        tindex        tinsert        treplace          tload
          tsubtract     tupdate

        In addition, you may not close, drop a table in read-
        only mode.  This is also the only case in which if t is
        a TEMP table, it will not be automatically dropped by
        any CRDE functions called in action.

        As you can see, "..if" functions provide almost
        unlimited querying capabilities.  You might think the
        these functions could replace their regular counterparts
        because you can do everything a search expression can
        do inside of action.

        However, there are several reasons why not to:

        - the most obvious is that "..if" function require you
        to write a user-defined function for every query.

        - CRDE can only take advantage your indexes if you
        specify a search expression.  For this reason you
        should always put as much of the query in the search
        expression as you possibly can.

        - and since action must receive a copy of every record
        for evaluation, "..if functions" are slightly slower
        than their regular counterparts.






                    - 158 -

        There are 7 "..if functions", each of which directly
        corresponds to one of the 7 "search related functions".
        All behave in a similar fashion as described above.
        They are: tprojectif, tdeleteif, tchangeif, tscanif,
        tlookupif, and tgetif, respectively.

        If t is a TEMP table then it is automatically dropped
        by tselectif, even if an error occurs.  Otherwise, t is
        unaffected by tselectif.

Return Value    tselectif returns a pointer to an open table if
        successful, or NULL if an error occurred.

See Also         tselect, tprojectif, tdeleteif, tchangeif, tscanif
        tlookupif, tgetif.

Example        /* query to select all suppliers who have a status >=
        the average status for suppliers from that city */

        int GTAvgForCity(void *rec)
        {
          int i;
          double d;

          i = taverage(tselect(TEMP, ((sup *)rec)->status, &d);
          if (i < 0)
            return i;
          return ((sup *)rec)->status >= (int)d;
        }

        answer = tselectif(STATIC, sups, GTAvgForCity, NULL);



----------------------------------------------------------------
tsort
----------------------------------------------------------------

Description      Sorts a table.

Declaration      TABLE *tsort(char *name, TABLE *t, char *id);

Remarks          tsort returns a copy of a table in sorted order.  The
        result table is physically sorted according to the sort
        order given by id.   id is any valid index descriptor
        (see tindex for a detailed description of index
        descriptors).  If t is a TEMP table then it is
        automatically dropped by tsort, even if an error
        occurs.  Otherwise, t is unaffected by tsort.

Return Value    tsort returns a pointer to an open table is successful,
        or NULL if an error occurred.

See Also         tindex.




                    - 159 -

Example          #include <stdio.h>
              #include "crde.h"

        int tbuffers = 128;

              int main()
              {
          TABLE *t;

          /* Display all suppliers with a status <= 5 ordered
          by supplier name */

          t = topen("sups");

          answer = tsort(STATIC,
            tselect(TEMP, t, "status", LE, 5, NULL),
            "sname"
          );

          tview(answer, NULL, NULL, 3, 3, 78, 23, 0x07, 0x07,
            0x07, NULL);

          tall(tclose);
        }



----------------------------------------------------------------
tstruct
----------------------------------------------------------------

Description      View the structure of a table.

Declaration      TABLE *tstruct(char *name, TABLE *t);

Remarks          tstruct returns the structure of a table as a table.
        It is useful for looking at table's structure if you
        forget the columns or primary key of a table.  A
        similar routine exists for examining the secondary
        keys on a table:  tkeys.

        The format of the struct table is equivelent to

            tcreat(name, "name c18, type c6");

        The rows in the struct table represent the columns in
        the table.  The name column contains the column name
        (preceded by an '*' if part of the primary key.) The
        type column contains the type specifier as would be
        specified in a table descriptor.

        If t is a temporary table then it is automatically
        dropped by tstruct, even if an error occurs.
        Otherwise,t is unaffected by tstruct.  In addition,
        altering the struct table has no effect t.



                    - 160 -

Return Value     tstruct returns a pointer to an open table if
        successful, of NULL if an error occurred.

See Also         tkeys.

Example         #include <stdio.h>
             #include "crde.h"

        int tbuffers = 128;

              int main()
             {
          TABLE *t;

          /* command line progam to view the structure of any
          table. */

          if (argc < 2) {
            printf("usage: tstruct <tablename>\n");
            exit(1);
          }

          if ((t = topen(argv[1])) != NULL)  {
            tview(tstruct(TEMP, t), NULL, NULL, 3, 3, 78, 23,
              0x07, 0x07, 0x07, NULL);
            tclose(t);
          }
          else
            printf("unable to open table.\n");
        }



----------------------------------------------------------------
tsubtract
----------------------------------------------------------------

Description      Removes records from one table which exist in another.

Declaration      long tsubtract(TABLE *t1, TABLE *t2);

Remarks          tsubtract removes records in one table which exist in
        another.  t1 and t2  must be compatible tables (see
        tunion for a description of compatible tables). t2 is
        unaffected by tsubtract.

        How tsubtract works depends upon whether t1 is keyed
        or not.  If t1 is keyed then it will lose all rows
        whose key values matches the key values of any record
        in t2.  If t1 is not keyed then it will lose any record
        which exactly matches any record in t2.

        If t2 is a TEMP table then it is automatically dropped
        by tsubstract, even if an error occurs.  Otherwise, t2
        is unaffected by tsubtract.



                    - 161 -

Return Value    tsubtract returns the number of rows subtracted if
        successful, or an error code if an error occurred.

See Also         tadd, tupdate.



----------------------------------------------------------------
tsum
----------------------------------------------------------------

Description      Calculate the sum of a column.

Declaration      int tsum(TABLE *t, char *c, void *result);

Remarks          tsum calculates the sum of column c in a table and
        places the result in result.  The column must be
        column must be of type i, l, f, or $.  Other types
        are ignored.  The result is always assumed to be of
        type double.

        If t is a TEMP table then it is automatically dropped
        by tsum, even if an error occurs.  Otherwise, t is
        unaffected by tsum.

Return Value     tsum returns 0 if successful or an error code if an
        error occurred.

See Also         taverage, tcount, tmax, tmin, tmax.

Example         #include <stdio.h>
        #include "crde.h"

        int tbuffers = 128;

        int main()
        {
          TABLE *t;
          double d;

          /* display total number of parts ordered by a supplier */

          t = topen("orders");
          tsum(tselect(TEMP, t, "s#", EQ, 100, NULL), "qty", &d);
          printf("Total number of parts ordered by supplier "
            "#100 = %ld.\n", (long)d);
          tclose(t);
        }










                    - 162 -

----------------------------------------------------------------
ttransact
----------------------------------------------------------------

Description      Begins a transaction.

Declaration      int ttransact(TABLE *t);

Remarks         ttransact begins a transaction on the table.  The
        table and all indexes are included in the transaction
        so you may continue to use the table just as you
        would normally.

        Any changes made to the table are recorded in memory.
        The transaction contines until a call to trollback,
        tcommit, tclose, or tdrop is made.  A call to tcommit
        or tclose will save all the changes made to the table.
        trollback or tdrop, on the other hand, discards the
        changes, returning the table back to the state it was
        in just prior to beginning the transaction.

        ttransact automatically flushes all buffers in t before
        starting the transaction to maximize integrity.

        Because CRDE keeps all transactions in memory, you may
        experience an -11 "Out of memory" error while performing
        transactions on very large tables.  If this occurs, you
        should immediately rollback the transaction before doing
        anything else.  However, even in the event of an error,
        trollback will be able to restore the original table.

        Calling ttransact while in a transaction has no effect
        on the table.

Return Value     ttransact returns 0 if succesful, or an error code if
        an error occurred.

See Also         tcommit, trollback.

Example        See tcommit.



----------------------------------------------------------------
tupdate
----------------------------------------------------------------

Description      Update records in one table with those from another.

Declaration      long tupdate(TABLE *t1, TABLE *t2);

Remarks          tupdate is similar to tadd except that records with
        identical primary keys are replaced by those in t2.
        If t1 is an unkeyed table, tupdate behaves exactly
        like tadd.



                    - 163 -

        t1 and t2 must be compatible tables, i.e. identical
        on a column by column basis in both type and size.
        They may have different primary and secondary keys
        however.

        If t is a TEMP table then it is automatically dropped
        by tupdate, even if an error occurs.  Otherwise, t is
        unaffected by tupdate.

Return Value     tupdate returns the number of rows updated if successful,
        or an error code if an error occurred.

See Also        tadd, tsubtract.

Example        #include <stdio.h>
        #include "crde.h"

        int tbuffers = 128;

        int main()
        {
          TABLE *sups, *recv;

          /* update the Sups table with an ascii file containing
          updates received from the home office */

          sups = topen("sups");
          recv = timportascii(STATIC, "*s# i, sname c26, city "
            "c26, status i", "recv.dat");

          tupdate(sups, recv);

          tclose(sups);
          tclose(recv);
        }



----------------------------------------------------------------
tunion
----------------------------------------------------------------

Description    Returns the union of two tables.

Declaration      TABLE *tunion(TABLE *t1, TABLE *t2);

Remarks          tunion returns the relational union of tables t1
        and t2.  t1 and t2 must be compatible, i.e.
        identical on a column by column basis in both type
        and size.  They do not have to have identical
        primary or secondary keys.

        The result of tunion is dependent upon whether t1
        is keyed or not.  tunion works by simply creating a
        copy of t1 and subsequently adding to it the records
        from t2.  If t1 is keyed, only those records from


                    - 164 -

        t2 which do not have a matching key in t1 will be
        added to the result. If t1 is not keyed, the result
        will contain all the records from t1 and t2.

        tunion is useful for ORing queries together.  For
        example, suppose you wanted a list of all customers
        whose age > 50 or salary < 5000.00.  You could
        construct the query as

              cust = topen("customer");
        answer = tunion(TEMP,
          tselect(TEMP, cust, "age", GT, 50, NULL),
          tselect(TEMP, cust, "salary", LT, 5000.00, NULL)
        );

        Note that this query will only work correctly if cust
        has a primary key.  Otherwise, the answer table may
        contain duplicates (if the are any customers who are
        both older then 50 and make less than 5000.00).

        If t1 or t2 is a temporary table then either, or both,
        are automatically dropped by tunion, even if an error
        occurs.  Otherwise, both t1 and t2 are unaffected by
        tunion.

Return Value     tunion returns a pointer to an open table is successful,
        or NULL if an error occurred.

See Also         tdiff, tintersect.



----------------------------------------------------------------
tview
----------------------------------------------------------------

Description     Views a table.

Declaration     int tview(TABLE *t, char *cl, char *id, int x1, int y1,
          int x2, int y2, int cattr, int fattr, int battr,
          void *rec);

Remarks          tview allows you to integrate powerful browse
        capabilities into your programs.  With tview you can
        view any table in any window on your screen, displaying
        only those columns you want in the order that you want.
        You can even use tview to select rows from a table.

        cl    Column list determining which columns to be
            displayed and which order they are to be
            displayed in.  If cl is NULL, then all columns
            are displayed in the order in which they were
            originally defined in the table.





                    - 165 -

        id      Index descriptor which describes the sort order
            the rows are to appear in.  id may be any legal
            index descriptor.  If id is NULL, then the rows
            are displayed in the order that they were
            originally inserted into the table.

              x1, y1, x2, y2
            Coordinates of the window to display the view
            in.  May be any window in 1,1 to 80,25 but must
            have a minimum height of 1 and a minimum width
            of 5.  tview automatically adjusts itself to
            the size of the defined window, allowing you to
            scroll both horizontally and vertically through
            the table.

        cattr      text attribute of column values
        fattr     text attribute of the view frame
        battr      text attribute for background (in window)

        rec     When tview returns, a copy of the rows occupied
            by the cursor is copied into the buffer pointed
            to by rec.  The buffer must be large enough to
            hold the record.  If rec is NULL, then the
            record is not copied.

              Cursor control in tview:

              Up             Go up a row.
              Down           Go down a row.
              Left           Go to the next column to the left.
              Right          Go to the next column to the right.
              Ctrl-Left      Go to the first column
              Ctrl-Right     Go to the last column
              PgUp          Go up a page of rows.
              PgDn           Go down a page of rows.
              Home           Go to the first row.
              End            Go to the last row.

              Enter          Exit returning decimal code 13.
              Esc            Exit returning decimal code 27.

        If t is a TEMP table then it is automatically dropped
        by tview, even if an error occurs.  Otherwise t is
        unaffected by tview.

              tview adds about 10k to your programs.

Return Value     tview returns 13 or 27 depending upon whether the
        user exits by striking Enter or Esc respectively.
        An error code is returned if an error occurred.

Example          #include <stdio.h>
             #include "crde.h"

        int tbuffers = 128;



                    - 166 -

        int main()
        {
          TABLE *t;

          /* view Sups table with columns reversed */
          t = topen("sups");

          tview(t,"status,city,sname,s#", NULL, 3, 3, 78, 23,
            0x07, 0x07, 0x07, NULL);

          tclose(t);
        }



----------------------------------------------------------------
twritec
----------------------------------------------------------------

Description     Puts a table in write-cache mode.

Declaration      int twritec(TABLE *t);

Remarks          twritec causes CRDE to buffer as many changes to a
        table as it can before flushing them to disk.
        Normally, CRDE writes all changes immediately to
        disk after every function call.  However twritec
        will prevent this, forcing CRDE to buffer as many
        changes as possible in memory before writing them
        to disk.

        twritec is useful when making a bunch of changes at
        once.  For example, a program might call twritec
        before inserting a number of records into a table,
        and then call tnormal when the operation is complete.
        Using this technique can have dramatic affects upon
        performance.

        On the other hand, doing something like:

        twritec(t);
        tdelete(t, "qty", LT 10, NULL);
        tnormal(t);

        won't provide any performance boost.  Why?, becuase
        all CRDE functions do internal buffering for the
        duration of the command.  twritec or tnormal only
        affect the way CRDE handles buffering after the
        function is completed.

        You can flush unwritten buffers at any time with
        tflush.  During the course of a program's execution,
        CRDE may flush them as well.  In either case, CRDE
        will continue to buffer changes until tnormal is
        called, or the table is closed or dropped.



                    - 167 -

        twritec has no effect on temporary tables, tables in
        a transaction, or tables already in writec mode.

Return Value     twritec return 0 if successful, or an error code if
        an error occurred.

See Also         tflush, tnormal.

Example          #include <stdio.h>
              #include "crde.h"

        int tbuffers = 128;

              int main()
              {
          TABLE *t;

          t = tcreat("class", "*studentid i, last c16, init c2, "
            "gpa f");

          twritec(t);  /* cache disk writes... */

          /* load the table with some students */
          tload(1522, "Adams", "D", 3.42);
          tload(1627, "Burns", "C", 3.92);
          tload(817 , "Bellamy", "J", 2.98);

          /*
            load rest of students here...
          */

          tclose(t);  /* unnecessary to call tnormal, tclose
                  automatically flushes buffers before
                  closing the table. */
        }



----------------------------------------------------------------
year
----------------------------------------------------------------

Description    Return the calendar year of a date_t value.

Declaration    int year(date_t d);

Remarks        year returns either 365 or 366 depending upon whether
        the date is in a leap year or not.

Return Value    year returns the calender year of the date, or -1 if
        the date is invalid.

See Also    day, month.

Example        See day.



                    - 168 -

----------------------------------------------------------------
yearadd
----------------------------------------------------------------

Description    Performs year addition/subtraction on a date.

Declaration    date_t yearadd(date_t d, int years);

Remarks        yearadd adds years years to d creating a new date.
        yearadd knows about leap years and correctly performs
        year addition so

            yearadd(mkdate(1, 1, 1992), 1)

        correctly yields 1/1/1993.

        You can subtract years from the date by placing a
        negative value for years.

Return Value    yearadd returns a date_t value or -1 if the date is
        invalid.

See Also    monthadd.



































                    - 169 -






Global Variables



----------------------------------------------------------------
tbuffers
----------------------------------------------------------------

Description    Number of buffers to allocate to CRDE.

Declaration    int tbuffers;

Remarks        Every program which uses CRDE must declare this
        variable globally in order for CRDE to run.  CRDE
        uses the value of this variable to allocate the
        memory buffers it will use to perform its operations
        dynamically at run time.

        Each buffer is 512 bytes in size.  CRDE requires an
        absolute minimum of 64 buffers (32 k) to run.  The
        more buffers CRDE has available to it the faster it
        will run.  Normally a value of 128 will suffice for
        most applications.  However, programs which use large
        tables, complex queries, and/or transaction tracking
        may require 256, 512, or more buffers to run
        effectively.

Example        Normally tbuffers is delcared something like this:

        int tbuffers = 128;   /* standard min setting */

        int main()
        {
          /* program here */
        }



----------------------------------------------------------------
terrno
----------------------------------------------------------------

Description      Holds error code of the last occurring error.

Declaration      int terrno;

Remarks          terrno holds the error code of the last error that
        occurred.  terrno is set whenever an error occurs,
        but is never cleared by any CRDE function.  However,
        you may clear it if you wish.  Thus, terrno is only
        valid for the current function if the function itself


                    - 170 -

        returns an error (either an error code, NULL, or
        in rare cases 0, depending upon the function).  If a
        function returns an error code, then terrno will be
        set to the same error code.  If a function returns
        NULL or 0 for an error you can use terrno to determine
        the cause of the error.

Example        #include <stdio.h>
        #include "crde.h"

        int tbuffers = 128;

        int main()
        {
          TABLE *t;

          t = topen("sups");

          if (!t) {
            printf("Unable to open table (error code = %d).\n",
              terrno);
            exit(1);
          }

          RestOfProgram();

          tclose(t);
        }



----------------------------------------------------------------
trowsfound
----------------------------------------------------------------

Description      Number of rows involved in last operation.

Declaration     long trowsfound;

Remarks          trowsfound holds the number of records changed, deleted,
        imported, etc., from the last operation.  trowsfound is
        set by most CRDE functions.

        In the event of an error, trowsfound can be used to
        detemine the number of rows processed before the error
        occurred.

Example        #include <stdio.h>
        #include "crde.h"

        int tbuffers = 128;

        int main()
        {
          TABLE *t;



                    - 171 -

          t = topen("sups");

          result = texportascii(t, "sups.dat");
          if (result < 0)
            printf("Export failed (error code = %d) after "
              "%ld rows.\n", terrno, trowsfound);
          else
            printf("Export successful.  &ld rows exported.\n",
              trowsfound);

          tclose(t);
        }














































                    - 172 -






Appendix A

Error Codes



General Errors


-1    General Error

    -1 is used as a general error code by CRDE.  The actual meaning
    of the error is completely context dependent.  CRDE may also
    generate a -1 code if it cannot determine the cause of an error.
    That situation, however, is rare.



Resource Errors


-11    Out of Memory

    This error occurs whenever there is not enough memory for CRDE
    to complete an operation.  Normally, this requirement is very
    low.  Because of the intelligent way in which it manages memory
    CRDE can perform even the most sophisticated operations in
    usually  < 32k.

    This error most commonly occurs when a large transaction has
    eaten up most available memory.  You should immediately
    rollback the transaction before continuing.


-16    Integrity Check

    CRDE tried to open a table which could not pass even the most
    basic of integrity checks.  It is very possible that the file
    that was opened was not even a CRDE table.  Otherwise, the table
    has a corrupt header, at the very least.  Use trepair to fix
    the table.



Operating System Errors


-21    Disk Seek

    A disk seek error occurred.



                    - 173 -

-22    Disk Read

    A disk read error occurred.


-23    Disk Write

    A disk write error occurred.


-24    File Create

    CRDE was unable to create a file.  This error will often
    occur when too many file handles are already open.


-25    File Open

    CRDE was unable to open a file.  This error can occur if
    too many file handles are open.


-26    File Close

    An error occurred while CRDE was trying to close a table.



Internal Errors


-31    Internal Error

    CRDE has detected an inconsistancy in its internal buffers.
    This is usually an indication of some kind of memory corruption.
    Your program may have accidentally written into the buffer
    area by way of an unallocated pointer.


-32      Internal Error

    Same as -31.



Key/Row Violations


-41    Key Exists

    CRDE tried to insert a row into a keyed table for which a key
    already exists.  -41 is unique in that it does not specify an
    error, rather an indication that the row was not inserted.
    Unlike other errors, CRDE will continue to process a function
    if an error -41 occurs.



                    - 174 -

-42    Key not found

    CRDE was unable to find a matching key for a row in the table.
    This is usually a sign of index corruption.


-51    Row previously deleted

    CRDE tried to delete aa row which was already marked as deleted.
    This is usually a sign of index corruption.



Parsing Errors


-101    Bad table name

    A table name must either be TEMP, STATIC, as defined in crde.h,
    or a legal DOS pathname <= 64 characters not containing wildcards
    or an extension.


-102    Token too long

    CRDE tried a parse a token which was too long for that particular
    context.


-103    Empty column list

    The column list, table descriptor, index descriptor, etc. must
    contain at least one column name.


-104    Column list too long

    There were too many columns listed in the column list for that
    particular context.


-105    Syntax error

    A general syntax error occurred.



Table Limit Errors


-111    Column too large

    A column may not be more than 256 characters in length.





                    - 175 -

-112    Key too large

    The combined total size of all columns in a key may not be
    larger than 256 bytes.


-113    Row too large

    The combined total size of all columns in a row may not be more
    than 4000 bytes.


-114    Too many columns

    A table may consist of a maximum  of 256 columns.  An index key
    may consist of a maximum of 8 columns.


-115    Too many indexes

    A table may not have more than 8 secondary indexes.


-116     No columns in this table

    A table must consist of at least one column.


-117    Too many tables

    CRDE imposes a restriction of no more than 128 open tables at
    any one time.



Syntax Errors


-121    Illegal datatype

    You specified an illegal datatype in a table descriptor.  Legal
    datatypes are c, i, l, d, f, and $.


-122    Unknown column

    You specified a column in a table which CRDE could not recognize.
    This error can often occur when you forget to end a search
    expression/change list with a terminating NULL.


-123    Unknown index

    You specified an index in a table which CRDE could not recognize.




                    - 176 -

-124    Duplicate column

    A column name appears more than once in a column list.


-125    Duplicte key column

    A key may not contain duplicate columns.



System Errors


-131    Invalid table

    A TABLE * does not point to an open table.


-132    Table is not modifiable

    You tried to modify a table which CRDE has placed in read-only
    mode.  CRDE does this whenever you call one of its "..if"
    functions.  The host table is placed in read-only mode until
    the "..if" functions terminates.


-133    Operation restricted by transaction

    Some CRDE functions are not permissible during a transaction.
    You may not create or drop secondary indexes during a
    transaction.


-134    Corrupt table

    CRDE has determined that a table is corrupt.  Use trepair to
    fix the table.


-135    Duplicate tables

    You tried to perform a two table operation on a single table.
    This is not allowed.


-136    Corrupt index

    CRDE has determined that one or more of a table's indexes are
    corrupt.  Rebuild the indexes.


-137    Table cannot be temporary

    Use of a temporary table, TEMP or STATIC, is not permissable
    in this context.


                    - 177 -

Search/Change Expression Errors


-141    Illegal relop

    You specified an illegal relop in a search expression.  Legal
    relops defined in crde. h are EQ, GT, LT, GE, LE, NE.


-142    Expression too complex

    A search expression may not contain more than 16 conditions.


-143    Too many changes

    A change list may not contain more than 16 changes.



Miscelleneous Errors


-151    Join columns do not match

    The join columns of two tables must be identical in both type
    and size on a column by column basis.


-152    Incompatible structures

    A two-table CRDE function requires that the tables have
    compatible structure, i.e. they must be identicial in both
    type and size on a column by column basis.  The tables may
    have different primary and secondary keys.


-153    Table is not tcrstruct compatible

    The first two columns of a table passed to the tcrstruct
    function must be c columns.  tcrstruct imposes no other
    restriction upon the table.


-154    Bad tview specs.

    You specified a bad window for the tview function.  A window
    must be within the coordinates 1,1 to 80,25 and must have a
    width of at least 5 and a height of at least 1.


-155    Unable to convert data

    An import or export operation was unable to convert data
    between    CRDE and the foreign data type.  This error may also
    occur when trying to change a date column to something other
    than a char column with trestruct.

                    - 178 -